Date() and Time() functions not returning values

[Mark A. Sam]

I am using Access2002 and getting errors in this code:

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("Order Entry Hold Status Notes",
dbOpenDynaset, dbSeeChanges)
rst.AddNew
rst![OrdID] = Parent.[OrdID]
rst![Date] = Date
rst![Time] = Time()
rst.Update

Run time error 2427. You have entered an expression that has no value.
This means the Date Function and Time function.

I placed a textbox in a form with the ControlSource, =Date() which
displayed the date without a problem

The libraries I am using are:

Visual Basic for Applications
Microsoft Access 10.0 Object Library
Microsoft Access DAO 3.6 Object Library
OLE Automation

Thanks in advance.

God Bless,

Mark A. Sam

 

[Chris]

The only thing that I can think if is that it is getting
lost because of the field names being reserved keywords.
Try renaming them: OrderDate and OrderTime.

Else, try rst("Date")

Also, I wouldn't have spaces in the name of the table. If
you need them, try

Dim dbs As DAO.Database
set dbs = CurrentDB
dim rst as DAO.Recodset
set rst = dbs.OpenRecordset("Select * from [Order Entry
Hold Status Notes]")


Chris

 

[Douglas J. Steele]

It's possible it's because you're using Date and Time as fields in your
recordset. You should never use reserved words for any of your own objects,
be they table fields, form controls, VBA variables, etc.

And while I realize it's not what you're asking, why are you storing Date
and Time separately? Use a single variable, and store both. It'll make your
WHERE clauses a lot simpler. If you need them separated for some reason,
create a query with computed fields DateValue([MyDateTimeField]) and
TimeValue([MyDateTimeField])

 

[Mark A. Sam]

It's possible it's because you're using Date and Time as fields in your
recordset. You should never use reserved words for any of your own objects,
be they table fields, form controls, VBA variables, etc.

That isn't the issue, although you are correct about using Key Word. I
always place brackets around field names so it has never been a problem for
me.

I tried assigning Date() and Time() to variables with the same result.

And while I realize it's not what you're asking, why are you storing Date
and Time separately? Use a single variable, and store both. It'll make your
WHERE clauses a lot simpler. If you need them separated for some reason,
create a query with computed fields DateValue([MyDateTimeField]) and
TimeValue([MyDateTimeField])

I just personally find it easier to work with when date and time are in
different fields. I don't want to have to parse the values out whenever I
need to reference them.

God Bless,

Mark

 

[Douglas J. Steele]

The fact that Date() and Time() aren't working by themselves is a pretty
good sign that there are either other objects with those names in your
database, or else you have a References problem.

Check the references first: it's easier.

Open any code module (or open the Debug Window, using Ctrl-G, provided you
haven't selected the "keep debug window on top" option). Select Tools |
References from the menu bar. Examine all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)

If that doesn't work, then search through all of your code to see whether
you're using Date or Time inappropriate somewhere.

--
Doug Steele, Microsoft Access MVP

It's possible it's because you're using Date and Time as fields in your
recordset. You should never use reserved words for any of your own objects,
be they table fields, form controls, VBA variables, etc.

That isn't the issue, although you are correct about using Key Word. I
always place brackets around field names so it has never been a problem for
me.

I tried assigning Date() and Time() to variables with the same result.

And while I realize it's not what you're asking, why are you storing Date
and Time separately? Use a single variable, and store both. It'll make your
WHERE clauses a lot simpler. If you need them separated for some reason,
create a query with computed fields DateValue([MyDateTimeField]) and
TimeValue([MyDateTimeField])

I just personally find it easier to work with when date and time are in
different fields. I don't want to have to parse the values out whenever I
need to reference them.

God Bless,

Mark

 

[Mark A. Sam]

Doug,

There doesn't seem to be a reference problem. This code inserts a record on
a subform. I opened the subform itself as a form and the record was
inserted and date and times assigned without a problem.

I renamed the Date and Time fields to Date1 and Time1 and that solved it.

Thanks.

God Bless,

Mark

The fact that Date() and Time() aren't working by themselves is a pretty
good sign that there are either other objects with those names in your
database, or else you have a References problem.

Check the references first: it's easier.

Open any code module (or open the Debug Window, using Ctrl-G, provided you
haven't selected the "keep debug window on top" option). Select Tools |
References from the menu bar. Examine all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)

If that doesn't work, then search through all of your code to see whether
you're using Date or Time inappropriate somewhere.

--
Doug Steele, Microsoft Access MVP

It's possible it's because you're using Date and Time as fields in your
recordset. You should never use reserved words for any of your own objects,
be they table fields, form controls, VBA variables, etc.

That isn't the issue, although you are correct about using Key Word. I
always place brackets around field names so it has never been a problem for
me.

I tried assigning Date() and Time() to variables with the same result.

And while I realize it's not what you're asking, why are you storing Date
and Time separately? Use a single variable, and store both. It'll make your
WHERE clauses a lot simpler. If you need them separated for some reason,
create a query with computed fields DateValue([MyDateTimeField]) and
TimeValue([MyDateTimeField])

I just personally find it easier to work with when date and time are in
different fields. I don't want to have to parse the values out whenever I
need to reference them.

God Bless,

Mark