Convert date to number (1 Viewer)

[grenee]

Good Day All.

I can convert a date in the form 1/12/2020 to a serial number, in the query window and it runs well. However it would not sort nor can a selection of the results be obtained. The error message is "Invalid use of Null"

Criteria for the selection is <2.

My expression for the date conversion is :

Can anyone provide any guidance please?

CLng(CDate([Account General Journal]![Journal Date]))

 

[jdraw]

Can you describe the requirement in simple, plain English?
What is the rationale for using a Date as a serialNumber?
Database works very well when using1 fact in 1 field.

You may get some insight from this article by Philipp Stiefel (codekabinett).

 

[Ranman256]

if you are referencing a form object in a query, then you need the full path:
forms![Account General Journal]![Journal Date]

if this is a form reference to a subform, then use the BUILDER to get the path correct:
usu: me.subfrmName.form.txtbox

but why do you need a serial date? dates are numeric and can be sorted like a number.

 

[arnelgp]

"Invalid use of Null"

check the table and the [Journal Date] field. the error is suggestive.

 

[grenee]

Can you describe the requirement in simple, plain English?
What is the rationale for using a Date as a serialNumber?
Database works very well when using1 fact in 1 field.

You may get some insight from this article by Philipp Stiefel (codekabinett).

Thanks for the Link. It enlightened my understanding and the problem is now solved

 

[jdraw]

Glad it is resolved. Happy to help.