Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Query based on date

Posted on 2013-05-31
17
Medium Priority
?
317 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
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

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 52

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

577 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