Solved

Query based on date

Posted on 2013-05-31
17
309 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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
 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

778 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