X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Append Query - 4 Fields Must Be Null., Access 2016    
post Mar 12 2018, 10:15 AM

Posts: 4
Joined: 12-March 18

Hello All,

I am trying to make an append query with the criteria that if all of the four date fields are null, then the record is not appended. FYI: A form (WeeklyData) is used to open a select query (WeeklyQuery), then the dates are filled in--but some records may be left blank, then the append query (WeeklyAppend) is run.

My fields are ID, Date1, Date2, Date3, Date4.

I tried an SQL statement:

Where ((IIf("Date1" Is Null, IIf("date2" Is Null, IIf("date3" Is Null, IIf("date4" Is Null, "No”, "yes"), ”Yes"), "yes"), “Yes”)) = "No")

but it does not work.

I am pretty new to Access, SQL & VBA, but I was hoping someone knew an easy fix to my problem. Help please! smile.gif
Go to the top of the page
post Mar 12 2018, 10:20 AM

Posts: 4
Joined: 12-March 18

I feel stupid. The moment I submitted this the answer popped into my head. I just needed to do "Is Not Null" under Or in the design view of the query.

Go to the top of the page
post Mar 12 2018, 10:24 AM

UtterAccess VIP
Posts: 8,467
Joined: 25-October 10
From: Gulf South USA

Hi: You might try something like ...

Where Not IsNull([date1]) Or Not IsNull([date2]) Or Not IsNull([date3]) Or Not IsNull([date4])


Note that you put the date fields in double-quotes, which makes them literals, not fields.

Beyond this, using four fields of date1, date2, etc. looks like you have repeating fields in a record - and a questionable, non-normalized design. But we'd have to see more to make further comments/suggestions on this.


I see you found a solution (although this thread should probably be left here as-is). But did you want to address the repeating fields?

"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
post Mar 12 2018, 10:53 AM

Posts: 4
Joined: 12-March 18


In reality.. the database is pretty simple. it is just creating a log of dates that show when our company's preventative maintenance is completed. We have weekly maintenance, but the forms are only filled out at the end of each month, so each date corresponds to a week of the month. With each of the dates, there is also an initial and completed status field. There are actually 17 fields used in the append query but I simplified it when posing my question. Is it taboo to do something like this usually?

I included an image of the form. Essentially the Save and Exit runs the append query and closes it. There is an event procedude on form open that clears all the fields with borders.

[External Link Removed]
This post has been edited by doctor9: Mar 12 2018, 12:12 PM
Go to the top of the page
John Vinson
post Mar 12 2018, 11:54 AM

UtterAccess VIP
Posts: 4,242
Joined: 6-January 07
From: Parma, Idaho, US

There are actually 17 fields used in the append query but I simplified it when posing my question. Is it taboo to do something like this usually?

It's a venial sin, punishable by being required to write "I Shall Not Commit Spreadsheet" 17 times on the blackboard and learn about Normalization smile.gif

This is normal design for a spreadsheet but is NOT valid for a relational database. Database tables should be tall and thin, not wide and flat; if you need to record four dates, you should have four records, not four fields.

In this specific case you're assuming every month has four weeks. Some don't! A 30 or 31 day month may well include parts of five weeks; if your maintenance is on Monday, you'll have five maintenance events just next month (April 2018). I'd (strongly) suggest using a bound continuous form in which you can see all four (or five!) records at once, and just enter the status and initials on each row. In fact if the status is just a yes/no field indicating completion, the field should simply not exist - you can instead check for a non-NULL initials field to find dates on which maintenance was done.

John W. Vinson
Wysard of Information
Go to the top of the page
post Mar 12 2018, 08:24 PM

UtterAccess VIP
Posts: 9,816
Joined: 10-February 04
From: South Charleston, WV


Can we see the actual query?

Robert Crouser

My company's website
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    16th July 2018 - 11:02 PM