Question Editting date value

[HandSam]

Hi all, I would like to add code in my database that will edit a table. The table has a field that stores a date value.
Everytime the program is run, it is to check if the date is the default date, and if it is, it is to change that date stored in that table, to the current date.

ie, if date in that table = default date,(eg 1/1/1990) then
set date in that table = current date today.

I had tried to change it using sql syntax but wasnt successful. Please assist.

 

[NeutronFlux]

What does your SQL statement look like? It should be similar to this

Function ChangeDate()
    Dim strSQL As String
    strSQL = "UPDATE [tablename] SET [datefield] = #" & Date & "# WHERE [datefield] = #01/01/1990#"
    Debug.Print strSQL
    CurrentDb.Execute strSQL, dbFailOnError
End Function

 

[HandSam]

Thank you very much Neutron, Let me try out your code, although I will not add the criteria part, because my table has one record of the institution details...

 

[Brianwarnock]

Why can you not just run an update query?

Brian

 

[HandSam]

Hi Neutron, I used your code and I am happy my table got editted but there is a funny anomally.
Here is the code I used:

Private Sub Form_Load()
Dim dateinstalled As Date

dateinstalled = DLookup("installationdate", "instdetails")

If dateinstalled = DateValue("March 12, 1990") Then

strSQL = "UPDATE [instdetails] SET [installationdate] = " & DateValue(Now())
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError
End If


The result is that the new value given to the table is 12:00:43. The date behind this time is Dec 30th 1899. I went to the design view of the table and restored the format of that field to short date, and rechanged it to 03/12/1990. But running the code again gives me the same anomally. What would be the problem?

 

[HandSam]

Hi BrianWarnock. I had thought of an update query but I am not so good with queries.
I do not know what to add to the : 'Update to:'
and also what to add to the : 'Criteria'.

 

[Brianwarnock]

Why use Now instead of Date when all you want is a date?

Brian

 

[HandSam]

Hi Brian, I want the system to set the date in that value to the current date, if the date in that field is the default ie if it is 03/12/1990, it should set it to the current date. What is the syntax of the current date?

 

[HandSam]

I changed the code to:

strSQL = "UPDATE [instdetails] SET [installationdate] = " & Date
but it gives the same result

 

[Brianwarnock]

=Date()


In a query that would be entered in the update to, tho the system removes the =
And in the criteria whatever you are looking for

Is Null
#1/1/1990#

brian

 

[jdraw]

As Brian said you should use Date() to get the Date in short date format. If you use Now() you will get the Date with a Time value also.

You should familiarize yourself with the various Date functions available at
http://www.techonthenet.com/access/functions/index.php

Also, UPDATE queries can be very unforgiving if you are not careful with constraints. You may inadvertently UPDATE all or many records just because the WHERE clause constraint was " a little off".

Good luck with your project.

OOOoooPs: I see Brian has responded while I was typing.

 

[Brianwarnock]

I've not done this in VBA but I doubt that Date needs to be in # as it is a function not a date string.

Brian

 

[NeutronFlux]

I've not done this in VBA but I doubt that Date needs to be in # as it is a function not a date string.

Brian

In this case, it does require it since it's building the SQL string. If you just do

"Set [InstallationDate] = " & Date

, the actual SQL will appear as

Set [InstallationDate] = 12/12/2012

which updates the table with a default time. That is what has occured with the OP.

@OP, it should be:

strSQL = "UPDATE [instdetails] SET [installationdate] = #" & Date() & "#"

 

[HandSam]

Thanks to all of you jdraw, Brian, and Neutron, My code finally worked . The final code was this line:
strSQL = "UPDATE InstDetails SET InstDetails.InstallationDate = Date() WHERE (((InstDetails.InstallationDate)=#3/12/1990#));"
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError

It would not have worked without your help, thank you very much, Thanks again

 

[HandSam]

I do not know if I can ask this here, but assuming I have this code:
Docmd.openform(name of form)

I would like to click a button that will comment this code so that the system will not execute it, ie
change it to
'Docmd.openform(name of form)
Is there an easier way to do this, eg by using goto?

 

[jdraw]

Making that line a comment seems easiest. Is there more to your question??
Are there conditions when you do and do not want the form to open?

 

[Brianwarnock]

In this case, it does require it since it's building the SQL string.

Fair enough, thanks

Brian

 

[spikepl]

@Brian

I've not done this in VBA but I doubt that Date needs to be in # as it is a function not a date string.

Even though already answered by Neutron Flux, the main thing to remember is whether you are assigning a value from a variable to a variable or from/to text. Variable-to-variable does not require # or formatting. When conversion to/from text is involved, then # and Format (for dates outside US format) are required.