convert string to date

[AmyLynnHill]

Greetings,
I have a query I want to run on the ON OPEN event of a form. I want it to run only once a day. I have the following code on the ON OPEN event

Private Sub Form_Open(Cancel As Integer)
Dim update As String

update = "Select GDCSearch.LastUpdate from GDCSearch "

If update < Date() Then
DoCmd.OpenQuery "qryGDC"
End If
End Sub

problem is it doesnt run the query. I think (fairly new at VB) it is due to the UPDATE variable is a string and I need to convert it to a date. Any suggestions on the approach or how to convert a string variable to a date?

 

[ghudson]

try

If Format([update],"mm/dd/yyyy") < Format(Now(),"mm/dd/yyyy") Then

or

If Format(CDate([update]),"mm/dd/yyyy") < Format(Now(),"mm/dd/yyyy") Then

 

[namliam]

Currentdb.Openrecordset("Select ...")
Will run your query, Look up the Openrecordset in the access help, let me/us know if you get stuck

 

[AmyLynnHill]

Ok I'm stuck

I tried the format syntax, it didnt work. the form opens but the query doesnt run which leads me to believe the IF stmt is FALSE so it ends. But in actuality it is TRUE.

I looked up the OpenRecordSet Method....not sure where/how I would use this.

 

[AmyLynnHill]

I put a MsgBox to display what value is being stored in the variable UPDATE. The value is the select statement in text?????
most likely from the quotes??
no wonder the if statement is not working.
What am I doing wrong with the select statement?

 

[ChrisO]

If the field GDCSearch.LastUpdate is a Date/Time field then this should work: -

Private Sub Form_Open(Cancel As Integer)
    Dim datLastUpdate As Date
    
    datLastUpdate = DMax("LastUpdate", "GDCSearch")
    
    If datLastUpdate < Date Then
        CurrentDb.Execute "Insert Into GDCSearch ([LastUpdate]) Values (#" & Date & "#)"
        DoCmd.OpenQuery "qryGDC"
    End If
    
End Sub

 

[AmyLynnHill]

Why is the syntax "Insert into" vs Select?

 

[ChrisO]

If you don’t save the last date it could run more than once a day.

 

[AmyLynnHill]

Thank you so much.....It works like a charm.
I want to be sure i understand what the select stmt is doing. Is it inserting a date into the table?

 

[namliam]

The point I was making is...
update = "Select GDCSearch.LastUpdate from GDCSearch "
Assigns the STRING into update it doesnt execute a query.
You need to use
Currentdb.Openrecordset to run a query, then use the resulting recordset to retrieve your data.

In this case DMax works just as nice, however be carefull with any D... Function because they can be a real nightmare.

 

[ChrisO]

Well, it’s not a select statement but yes it is inserting the current date into the table.
(Have a look in the table, it should be there.)

 

[AmyLynnHill]

Got it. Thanks a bunch! greatly appeciated.
good day!