Solved

Query based on date

Posted on 2013-05-31
17
307 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
 

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 40

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 49

Expert Comment

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

/gustav
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 250 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: 39212563
0
 

Author Comment

by:CMILLER
ID: 39212564
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now