Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Query based on date

Posted on 2013-05-31
17
Medium Priority
?
315 Views
Last Modified: 2013-07-15
Hi, I need two (2) queries. One that will show if there is a date in the field and One that shows if the field is blank.

Please see the attached database.
Cub-Scout.accdb
0
Comment
Question by:CMILLER
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
17 Comments
 
LVL 12

Expert Comment

by:pdebaets
ID: 39212360
Select IsDate(MyField) as FieldIsDate from MyTable;

Select iif(trim("" & MyField) = "",True,False) as FieldIsBlank from MyTable;
0
 

Author Comment

by:CMILLER
ID: 39212405
Its not working.
0
 

Author Comment

by:CMILLER
ID: 39212406
Did you view my attached DB?
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

Author Comment

by:CMILLER
ID: 39212409
I guess what I was wanting was for the query with a date in the field was to just display the fields and show the dates.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39212415
You just have one record in your db. Can you post your expected result?
0
 

Author Comment

by:CMILLER
ID: 39212416
0
 

Author Comment

by:CMILLER
ID: 39212419
First, I will have at least 10 names in the DB. in the attached DB-1 it shows the there is a date in the Aquanaut Date, Engineer Date, Fitness Date and Forester, etc... I want the query to only show fields with dates and I want the query to show the date. I dont want the blank ones.

The next query I need is the opposite, I want to see all the fields with blanks (no dates)
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 39212443
Sounds and looks like a spreadsheet to me.
Why not use Excel for this?

/gustav
0
 

Author Comment

by:CMILLER
ID: 39212449
All of my information comes from xls exports from an online web database. I dont like their reports and need to create my own. I have about 10 diff tables for the same 10 scouts.
0
 
LVL 12

Expert Comment

by:pdebaets
ID: 39212486
What if a record has dates in some fields, but other date fields are blank? Then what do you want to show?
0
 

Author Comment

by:CMILLER
ID: 39212526
If the field is blank, dont show it.

I want to only show fields with dates.
0
 
LVL 12

Expert Comment

by:pdebaets
ID: 39212535
Select ("Aquanaut Date: " + [Aquanaut Date]) & (", " + ("Artist Date" + [Artist Date])) & (", " + ("Athlete Date" + [Athlete Date])) & ...
From Miller;
0
 
LVL 12

Accepted Solution

by:
pdebaets earned 1000 total points
ID: 39212546
Oh, now I see what is going on... these are apparently merit badge earned dates. You should have a separate table listing your merit badges, then a table that links the merit badges earned with the person and the date they were earned. You need more than one table (i.e.: a "relational database") to handle what you are trying to do.

Here's a starter table design:

TableName: People
Fields:
PersonID (auronumber primary key)
FirstName
MI
LastName

TableName: MeritBadges
Fields:
MeritBadgeID (auronumber primary key)
MeritBadgeName

TableName: PersonMeritBadges
Fields:
PersonMeritBadgeID (auronumber primary key)
PersonID (Data Type: Long, Foreign Key to People table)
MeritBadgeID (Data Type: Long, Foreign Key to MeritBadge table)
DateEarned (DataType: Date/Time)

Get that going first, then let's talk about your queries.
0
 

Author Comment

by:CMILLER
ID: 39212566
Here are the two main files that I export from a master web site. I have removed the names.
0
 

Author Comment

by:CMILLER
ID: 39225780
I provided two files from the master web site database. what else do I need to provide?
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question