Invalid Use of Null

[Runawaygeek]

Hi,

so i have built a query that takes a date cell and a time cell and puts them into a new cell as "dd/mm/yyyy hh:mm:ss"

Publish_DT: [Schedule Date] & " " & Format((TimeValue([Schedule Time])+TimeValue([Duration]))-(TimeSerial(0,15,0)),"Long Time")

Then i have another query that takes a Date Time String from another table (cant change this as field can be anything for other service types) and i use CDATE(TEXTFIELD)
This outputs in the Query as "dd/mm/yyyy hh:mm:ss"

I then have a further query that compares these two values,

EXPR: FORMAT([DT_1]=[DT_2], "Y/N")

but when i do this, i get "Invalid use of null" WHY???

Thanks,
Ben

 

[Ranman256]

you cant perform math on NULLs

you can convert
format(nz[DT_1])=nz([DT_2]),"Y/N")

 

[Runawaygeek]

you cant perform math on NULLs

you can convert
format(nz[DT_1])=nz([DT_2]),"Y/N")

Thanks Ranman,

There are no null values, which is why i am confused.
However, i did notice that on the CDATE formula it outputs 01/01/2016 00:00:00 as just 01/01/2016 and fails to print the time value. I might plus 1 second on it and see if that helps.

your NZ solution still failed to the same error??

 

[Ranman256]

there must be, maybe in
Format((TimeValue([Schedule Time])+TimeValue([Duration]))-(TimeSerial

you don't do time math your way,
DAteDiff("n",date1,date2)

 

[Runawaygeek]

there must be, maybe in
Format((TimeValue([Schedule Time])+TimeValue([Duration]))-(TimeSerial

you don't do time math your way,
DAteDiff("n",date1,date2)

Thanks, i have re-written to your DATADIFF, which makes more sense.
But i still get the error.

there are no null values in the queries i am applying the maths too.
Is there a way to check that the fields are definitely outputting a "date" value?

 

[Runawaygeek]

OK, there is an issue with my Date Time concatenation, some values are pulling in as string.. I think i need a query between these to change the source to Date and time.

 

[The_Doc_Man]

You can always change a date to a date (i.e. use CDate whether or not it was needed.) So don't bother to test whether the fields are string or date. Just CDate them.

As to having a date input return a time of "00:00:00" - that is perfectly normal. Internally, date fields in Access are a cast (a.k.a. typecast) of a DOUBLE, showing you the time in days and fractions thereof since the system reference date. If your source was a date-only string, it converts to a date with no fractions - which corresponds to midnight of that date. As to how a Null finds its way in, a date syntax error on a conversion routine might sometimes silently return nothing.

 

[Runawaygeek]

Right, all my data is pulled through the range of queries as CDATE.
Access can see everything as a date type as it offers date filters when you choose filter.
BUT, i am still getting told that i have an invalid use of null.
There are no null values on any of the quieres, just on the source table that imported first.
I have a query that removes all the null rows and only lets through the ones that meet the DATE/TIME format.

Any ideas?

thanks