Date Is Null

[sanders720]

I have a form with check boxes. When I check the box, I have code that populates a table field with todays date.

This, upon clicking the check box, I want to...

Tables!tblABC.fieldname.value = Date

First, I am wondering idf there is anything wrong with this syntax. However, if I do something basic like...

MsgBox Date on the control, I get an Invalid Use of Null error. I'm confused because shouldn't Date be today's date?


Thanks in advcance for any help you can provide.

 

[nimarii]

try using Now instead of Date:
Tables!tblABC.fieldname.value = Now

You might also want to use text instead of value:
Tables!tblABC.fieldname.text = Now

Using now might also give you the time, but I think if you format the field as type "short date" it should appear normally.

 

[sanders720]

No Go... In fact, exactly the same result.

 

[nimarii]

hm, if the date is appearing in your form, then I don't see how it could be reading a null value. maybe instead of using Tables!tblABC.fieldname.text try using me.fieldname.text?

Also, when you are using msgbox, are you setting the focus to the control before reading the value?
me.fieldname.setfocus
msgbox me.fieldname.text

 

[RoyVidar]

If it is a control on the form, bound to a table field, use the name of the control with the Me keyword:

[tt]Me!txtMyDateControl.value=date[/tt]

To "refer" to table fields (not represented by controls on the form), you'll need to use sql/recordset in some way, for instance an update query (here executed with ADO):

[tt]Dim strSql as String
strSql="update mytable set mydatefield =#" & format(date,"mm/dd/yyyy") & _
"# where somefield = " & me!txtSomeControl.value
currentproject.connection.execute strSql[/tt]

- the formatting of the date is needed if your regional settings differ from US date format.

Roy-Vidar

 

[willir]

Sanders

Roy is correct.
And DATE will work. DATE returns the current system date. Now returns current Date + Time.

There are a couple of gotchas.

It seems you know how to enter visual basic coding. How are you running the code. You "check" a box and this inserts the date. What happens if you uncheck the box?

Next, you have to ensure that you are referencing the correct name of the text box.
- Open the form in design mode. Make sure you have the "Properties" window open. (From the menu, select "View" -> "Properties")
- Select your date text box.
- Now select the "Other" tab on the Properties window and look at the "Name" field - the value here is the name used by Access. MAKE sure this is how you spelt the field in your code.
- Now select your Check box - you can take note of the name of this control too.
- Click on the "Events" tab in the property window. You should see an "event" with an "Event Procedure" - perhaps "AfterUpdate".
- If you select the field with the "EventProcedure", and click on the "..." command button, Access will take you to your code.

Richard

 

[sanders720]

The Control Source for the checkbox is not set to anything. In the form, when clicked is supposed to fill in the corresponding date field. VBA code is what corresponds the fields, nothing else.

Ultimately, on load, of the length of the date field is >0 then I will also show the corresponding checkbox as chedked.

Any thoughts though, on the Date thing. A co-worker says this is an Access bug. It usually works, but sometimes it does not. He says if I pout a textbox on the form and do a MsgBox txtDate.Value it will work. I haven't tried this yet.

The real mystery is that I cannot even do a MsgBox Date, which is not dependent on any data or condition!

 

[Zion7]

Sanders, it sounds very bizarre?
is this problem, throughout the whole database, is it just on this form module, or standard module?

I know you tried MsgBox, but does the field that you are attempting to enter a date value, not accept dates?

I personally, have never had any problems in using Date or time, in various scenarios.

For the sake of asking, is your checkbox procedure properly syntaxed?

Private Sub chkDate_Click()

Select Case chkDate
Case -1: txtDate = Date
Case 0: txtDate = ""
End Select

...sorry Sanders, I had to ask.

Outside of that, I would trouble shoot a bit.
try different forms or modules.
Maybe store it in a variable first...

Sub TroubleShootToSeeWhetherOrNotDateFunctionIsWorking() ...LOL
Dim dDate As Date, sDate As String
dDate = Date
sDate = Date
Debug.Print dDate
Debug.Print Date
Debug.Print sDate
Debug.Print Format(sDate,"d/mmm/yy")
MsgBox dDate
MsgBox Date
End Sub

Your first syntax for the form field, aroused my curiosity.
Tables!tblABC.Field....(incorrect, but interesting)
Forms!frmABC.txtDate.Value or Me.txtDate.Value (correct)

Outside of that Sanders, I'm at a loss?

Hope you figure this out, Good Luck!

 

[RoyVidar]

Check for a reference marked as missing/invalid... (VBE - Tools | References)

Try prefixing the date with the library:

[tt]vba.date[/tt]

Also try with or without paranthesis.

Sometimes using [tt]int(now)[/tt] does the trick (now returns date/time, the conversion leaves only the date part)

If any of this works, I'm inclined to believe it is a symptom of some kind of problem, perhaps a sligth corruption, something with the install (or as mentioned, the references)?

Roy-Vidar

 

[sanders720]

This is right, and thanks for the help!