fill a date field with some value if is empty

[eshai]

i have a date field on a form where the data is coming from a table
now i notice the in the table the field is empty(Serious employee failure)

i can put any date that i want in the field(It will be corrected later)

first i did a check

=IIf([rundate],IsNull(Date()))

i get the result "1/1/1899"
what is "1899"

 

[plog]

It's essentially a default date.

Explain exactly what this is testing:

=IIf([rundate],IsNull(Date()))

Let me put your code it into english:

If [rundate] is true then tell me if the current date is null.

Why would you do that? Is rundate a Yes/No field? Date() will never be null. Very confusing. What exactly where you trying to do?

 

[isladogs]

And to add to plog's comments, Iif statements need both a true part and a false part.
Your IIf statement makes no sense at all.

 

[eshai]

It's essentially a default date.

Explain exactly what this is testing:

=IIf([rundate],IsNull(Date()))

Let me put your code it into english:

if "rundate" is empty put the current date
all the Is function return the same value

 

[eshai]

And to add to plog's comments, Iif statements need both a true part and a false part.
Your IIf statement makes no sense at all.

ok =IIf([rundate],IsNull(Date(),1/1/2019))
now the false part 1/1/2019

 

[isladogs]

ok =IIf([rundate],IsNull(Date(),1/1/2019))
now the false part 1/1/2019

Nope.
As plog has already said, Date() will never be null. Also you need # delimiters for dates.
Suggest you explain in simple words what you are trying to do without using any code.

 

[eshai]

ok sorry

if [rundate] is empty then [some_date_field] else return the original value from [rundate]
(where [some_date_field] is based on "dateadd" field on the form)

 

[theDBguy]

ok sorry

if [rundate] is empty then [some_date_field] else return the original value from [rundate]
(where [some_date_field] is based on "dateadd" field on the form)

Hi. Pardon me for jumping in, but since you started out with an expression, I'll suggest you try the following:

=Nz([rundate],Date())

or maybe something like:

=Nz([rundate],[some_date_field])

Hope it helps...

 

[isladogs]

DBG beat me to it. I agree. Use Nz

 

[eshai]

Hi. Pardon me for jumping in, but since you started out with an expression, I'll suggest you try the following:

=Nz([rundate],Date())

or maybe something like:

=Nz([rundate],[some_date_field])

Hope it helps...

Both return an error #size!

 

[theDBguy]

Both return an error #size!

Hmm, what data type is [rundate]?

 

[plog]

I'm not entirely clear on what you are doing.

1. Are you trying to fix past data errors? If so, you should run an UPDATE query to fix those in your table.

2. Are you trying to prevent future data errors? If so, I don't think a VBA hack is the way to go. Instead you should solve this at the table level by making the field required and have a Default of Date() and at the form level of making the default value Date().

 

[eshai]

the date is "date field"

plog: I'm not trying to fix anything some Employees are missing data entry
and the "rundate" Used for a calculated field So if there is no data, the form will give error on loading to prevent that i have to put some date in the "rundate" field I don't care if it's true or not It will be fixed immediately I tried to set a default date()

I know that many times people simply bypass errors as #error! #func! etc' if you can fix it in the upper level Obviously it's better

 

[theDBguy]

the date is "date field"

Hi. Assuming you meant to say "rundate" is a date field, then try the following query and let us know if you see any anomalies:

SELECT rundate, IsNull(rundate) As Empty, Nz(rundate,"Anomaly") As Anomaly
FROM TableName

Do you see any #Size errors?

 

[eshai]

Hi. Assuming you meant to say "rundate" is a date field, then try the following query and let us know if you see any anomalies:

SELECT rundate, IsNull(rundate) As Empty, Nz(rundate,"Anomaly") As Anomaly
FROM TableName

Do you see any #Size errors?

its an sql function im working on a field in form not a query

 

[theDBguy]

its an sql function im working on a field in form not a query

Hi. I know that, but I'm still working the data angle. The above query will just let me know how your data is doing. Applying the Nz() function against a field evaluates the data in it. And if you're getting a #Size error, I wanted to see what kind of data is causing it. So, basically, I'm just trying to do some long distance troubleshooting right now to try and help you with your original problem.

 

[eshai]

I thought of such an approach

one field is called "rundate"
create another field named "nulldate" Which has a default date

and use if [rundate] isnull then [nulldate] else [rundate]

 

[eshai]

tnx thedbguy

Nz([rundate])

did the trick so no #error! show i add some code to mark the record as date error

 

[theDBguy]

tnx thedbguy

Nz([rundate])

did the trick so no #error! show i add some code to mark the record as date error

Ah, okay. Glad to hear you got it sorted out. Good luck with your project.

 

[isladogs]

PMFJBI but its always better to include the false part of an Nz expression so you can control what Access uses when the RunDate is null.
e.g. Nz(RunDate, "") or Nz(RunDate, Date()) etc