Date Function

[V. Roe]

I am using Access 2000 and Windows 98.
I have a form with a textbox and command button. I am trying to use the
command button to place today's date in the textbox. When I use the "Now"
function, I get a date and time, but when I use the "Date" function, I get
the following error message: "MA can't find the field "Date" referred to in
your expression". I have tried using = Date(), but the parenthesis
disappear. I have checked references as well. I am very new to Access and
would appreciate any help you can give. Below is the code I am using.
Thanks
Valerie

Private Sub DateCmd_Click()
Me.DateText.SetFocus
Me.DateText = Date & Format(DateText, "mm/dd/yy")

End Sub

 

[Dirk Goldgar]

I am using Access 2000 and Windows 98.
I have a form with a textbox and command button. I am trying to use
the command button to place today's date in the textbox. When I use
the "Now" function, I get a date and time, but when I use the "Date"
function, I get the following error message: "MA can't find the
field "Date" referred to in your expression". I have tried using =
Date(), but the parenthesis disappear. I have checked references as
well. I am very new to Access and would appreciate any help you can
give. Below is the code I am using. Thanks
Valerie

Private Sub DateCmd_Click()
Me.DateText.SetFocus
Me.DateText = Date & Format(DateText, "mm/dd/yy")

End Sub

You've checked references, and presumably didn't find any marked as
missing. Do you have a variable or object somewhere named "Date"? The
fact that the parentheses disappear isn't significant -- that happens
naturally in VBA code that uses the function. If you press Ctrl+G to
open the Immediate Window, what happens if you type

?Date

and press enter? What about

?VBA.Date

?

I'm not sure I understand what you're trying to do with this line:

Me.DateText = Date & Format(DateText, "mm/dd/yy")

I'd expect either

Me.DateText = Date

or

Me.DateText = Format(Date, "mm/dd/yy")

 

[V. Roe]

Thanks for your reply
No variables or objects called "date"
No missing referrences
In Immediate window "?date", I get the same runtime error 2465
In Immediate window "?VBA.date", I get the date
I tried both Me.DateText = Date and
Me.DateText = Format(Date, "mm/dd/yy") and get error 2465.
However
Me.DateText = Format(Now, "mm/dd/yy") did return date in short form. This
will work for me, but my question is can this cause problems if I want to
query the information based on the date?
Thanks again for your help.
Valerie

 

[Dirk Goldgar]

Thanks for your reply
No variables or objects called "date"
No missing referrences
In Immediate window "?date", I get the same runtime error 2465
In Immediate window "?VBA.date", I get the date
I tried both Me.DateText = Date and
Me.DateText = Format(Date, "mm/dd/yy") and get error 2465.
However
Me.DateText = Format(Now, "mm/dd/yy") did return date in short form.
This will work for me, but my question is can this cause problems if
I want to query the information based on the date?
Thanks again for your help.
Valerie

It's certainly behaving like a broken reference. I suggest you follow
Doug Steele's directions, posted here:


http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html

to force your references to be refreshed even if nothing shows as
missing.

You can make your code work, I expect, by writing

Me.DateText = VBA.Date

but I'd still be concerned that you have either a broken reference or a
redefinition of Date, either of which is going to cause you trouble down
the road. If your DateText control is a text box bound to a field of
type Date (not, please, named "Date"!), then assigning a Format(Now,
"mm/dd/yy") to it will work as far as getting just the current date into
the field, but it's a kludgey workaround.

 

[V. Roe]

Sorry to say, I tried all the things recommend in the site you listed, and
still no change. When I started my database, I did name the field "Date",
but have since renamed it (learned that was not a good thing). It is a
Date/Time type field. I have had a great deal of problems with strange bugs
and errors, but I am so very new to Access I'm not sure if it is my mistake
of problems I am having with computer. IT has reinstalled Access once, and
I'm still having problems. Thanks for trying. VBA.Date is working fine.

 

[Dirk Goldgar]

Sorry to say, I tried all the things recommend in the site you
listed, and still no change. When I started my database, I did name
the field "Date", but have since renamed it (learned that was not a
good thing). It is a Date/Time type field. I have had a great deal
of problems with strange bugs and errors, but I am so very new to
Access I'm not sure if it is my mistake of problems I am having with
computer. IT has reinstalled Access once, and I'm still having
problems. Thanks for trying. VBA.Date is working fine.

Did you mention what version of Access this is? If it's A2K or later,
try turning off the Name AutoCorrect option (on the General tab or the
Tools -> Options... dialog). I don't know if this is plausible or not,
but since you once had a field named "Date", it occurs to me that Access
may just possibly be remembering that and trying to "fix things up" for
you.

 

[V. Roe]

Thanks again, but still no luck. I'm using Access 2000 with Windows 98. I
have no wizards or vba help so believe when I say I really appreciate your
help. I'm trying to learn Access just using a "Dummies book" and the
newsgroups.
Valerie

 

[Dirk Goldgar]

Thanks again, but still no luck. I'm using Access 2000 with Windows
98. I have no wizards or vba help so believe when I say I really
appreciate your help. I'm trying to learn Access just using a
"Dummies book" and the newsgroups.

I'm glad to help, though I don't seem to have been much help yet. If
your database is small, you may e-mail me a zipped copy, if you like.
Please first remove any unnecessary data from the copy you intend to
send, then compact it, using Tools -> Database Utilities -> Compact and
Repair Database..., and then use a zip compression utility to shrink it
down to a manageable size. Then send the zip file to the address you
get by removing NO SPAM from the reply-address of this message.

 

[Ken Snell]

PMFJI...

My experience has been that sometimes ACCESS has a long memory when you name
a field Date and then change it later. I have had an instance on a form
where I had done exactly what you did, and could not get ACCESS to forget
the old name. Finally had to delete the control and field that had been
named Date (and, IIRC, may even have had to rebuild the form by importing
the objects into a new form), rebuild them, and then recompile ACCESS. Not a
fun problem.

 

[Dirk Goldgar]

I'm glad to help, though I don't seem to have been much help yet. If
your database is small, you may e-mail me a zipped copy, if you like.
Please first remove any unnecessary data from the copy you intend to
send, then compact it, using Tools -> Database Utilities -> Compact
and Repair Database..., and then use a zip compression utility to
shrink it down to a manageable size. Then send the zip file to the
address you get by removing NO SPAM from the reply-address of this
message.

Valerie -

I've now looked over the database copy you sent me and figured out how
to fix the problem. Let me say right up front that it's not your fault!
Well, only to the extent that you originally had a field named
Date, but you would think that when you renamed it Access would stop
having problems. You'd think that, but you'd be mistaken.

Ken Snell was right about what was going on. I noticed that if I began
editing the form's code module and started typing "Me.Dat", among the
items listed in the intellisense drop-down list was "Me.Date". That
could only mean that Access thought the form had a property or method
named "Date". Of course it doesn't, but when you created the form it
did, because Access creates a property for every control on the form and
every field in the form's recordset. For some reason -- I think we may
safely call it a bug -- Access created this property and didn't destroy
it when you renamed that field.

I found a couple of ways to eliminate the problem. My first approach,
though successful, was more complicated than it needed to be so I won't
describe it in detail (note for Ken: SaveAsText, LoadFromText). Then I
had a much simpler idea, tried it, and it worked: Valerie, here's what
to do:

1. Open the form in Design View.

2. Open the property sheet for the form.

3. On the Data tab, clear the Record Source property, leaving it blank.

4. Save and close the form.

5. Open the form in Design View again.

6. Reset the Record Source property to the table in question; in this
case, LogTable.

7. Save the form.

For me, that fixed it, removing "Date" from the list of form properties.
Once you've done that, you can (and should) go back into the form's code
module and edit the Click event procedure for the command button,
changing it simply to:

Private Sub DateCmd_Click()
'Enters today's date
Me.DateText.SetFocus
Me.DateText = Date
End Sub

If you get the same results I did, this will now work just fine.

By the way, I hope you realize that it's not necessary to set the focus
to DateText before setting its value. I'm assuming you have the
SetFocus line in the event procedure because you really want the focus
to go there, not because you think you have to do that before you can
assign a value to the control.

 

[Dirk Goldgar]

PMFJI...

My experience has been that sometimes ACCESS has a long memory when
you name a field Date and then change it later. I have had an
instance on a form where I had done exactly what you did, and could
not get ACCESS to forget the old name. Finally had to delete the
control and field that had been named Date (and, IIRC, may even have
had to rebuild the form by importing the objects into a new form),
rebuild them, and then recompile ACCESS. Not a fun problem.

Good call, Ken. This does appear to have been the problem. I found a
simpler way to fix it in this case, though -- have a look at my reply to
my own reply in this thread.

 

[V. Roe]

Thanks so much!!! That worked fine. I appreciate everything especially
keeping it simple.
Thanks
Valerie

 

[Ken Snell]

Yep, your solution is much simpler than what I'd had to do! Thanks,
Dirk...this one is now filed away for when I'm asked to correct someone
else's error < g >