Solved

Query based on date

Posted on 2013-05-31
17
314 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

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 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: 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

Use Filtering Commands to Process Files in Linux

Learn how to manipulate data with the help of various filtering commands such as `cat`, `fmt`, `pr`, and others in Linux.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

615 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