Of Nulls and Dates (1 Viewer)

LegionKilo · Jan 19, 2018

Good Afternoon,

(MS-Access 2017)
I have 7 date fields that have to be filled in to keep track of the training that personnel have completed. Unfortunately the data is unbelievably incomplete, and those that are supposed to be responsible for the data just don't have it.

Within the record for any particular person some of the date fields are filled in, others are not. I want to create a query that will only produce results for people that have something entered into any date field. I want to exclude records that don't have anything entered into all 7 date fields. :banghead:

Note: I'm not really capable of writing SQL just yet, but I'm learning it

Thank-you in advance.
Abe

RuralGuy · Jan 19, 2018

Have you tried the Query Builder? It's pretty smart.

MarkK · Jan 19, 2018

There should not be multiple date fields in the same row as you've described. When you add a single data point, you should add a whole row. Then, if there is data, there is a row. If there is no data, there is no row. You should never have a single row with multiple "sibling" data points.

If one row has many related data points, then those related points should be in related table, with a one-to-many relationship between the tables. Otherwise you encounter exactly the hazard you've described.

hth
Mark

arnelgp · Jan 20, 2018

Create a criteria for your query

IsNull(field1 & field2 & field3 & field4 & field5 & field6 & field7)

bastanu · Jan 20, 2018

Hi there Abe,

All you need is to create a query to include your person(al) info such as person name. id, etc. and in the 7 date fields to put Is Not Null on separate rows (the rows stand for "Or"). Please review the screen shots attached and you'll see it is quite easy to get what you want.

Cheers,
Vlad

LegionKilo · Jan 22, 2018

Bastanu,

You've helped me out. I appreciate the graphics. I actually have my tables setup in the exact way you've shown in the .png files, but my "null" statement might not be written correctly.

arnelgp · Jan 22, 2018

if all seven fields are Null, then,

Field1 & Field2 & Field3 & Field4 & Field5 & Field6 & Field7 = Null

or simply

Null & Null & Null & Null & Null & Null & Null = Null

whereas:

Null & "anything" = "anything"
or
"anything" & Null = "anything"

but since Null cannot be compared to anything,
we used IsNull() function.
This function will return True if the expression
inside the Parenthesis is Null.

therefore in your query:

SELECT * FROM yourTable Where IsNull(Field1 & Field2 & Field3 & Field4 & Field5 & Field6 & Field7)=False

you don't need to use OR those fields since you
are only interested on Records which have
at least a Date value on any field (Post #1).

jdraw · Jan 22, 2018

abe,

Further to Markk's advice, here are a couple of things to help with your research into database design and planning.
http://www.accessforums.net/showthread.php?t=65220&p=352133#post352133

Of Nulls and Dates (1 Viewer)

LegionKilo

New member

RuralGuy

AWF VIP

MarkK

bit cruncher

arnelgp

..forever waiting... waiting for jellybean!

bastanu

AWF VIP

Attachments

LegionKilo

New member

arnelgp

..forever waiting... waiting for jellybean!

jdraw

Super Moderator

Attachments

Similar threads

Users who are viewing this thread