I guess you didn't read the second sentence. He really wants time and time really makes sense in this context. As soon as the SQL works with just the date, it needs to be modified to work with date AND time. I offered two solutions both of which will avoid any problem caused by formatting the date as a string that includes a.m.
Hi Majp; Thanks for your help. Now I have tried with your code but the outcome is still the same. I defined (dim) at the beginning of the event. I even tried defining at the beginning of the query but in the end, was the same outcome. What could be the reason?
Are you using an unbound form or are you duplicating the whole record? What is the purpose of saving the date/time? You can't use Date() if you want the time also. You must use Now().
You might have better luck if you don't use embedded SQL. Use an append query. That gets the date/time from the form field.
Well, the main purpose of saving the date / time is to have a tracking of the date when a user submits the form (when a user fills out the form and save the data to another table)
I was using Now() buy there was a problem with the configuration??
How to use an append query?? I´m new on this and I´m learning
No, the Debug.Print output in the VBA Immediate Window which is located below the code window, assuming it is active. Google "Access VBA Immediate Window" and "Access VBA Debug.Print". Here is one tutorial https://www.techonthenet.com/access/tutorials/vbadebug2010/debug07.php
Learn debugging techniques.
Append query creates a new record. You said you want to update existing record.
I made a suggestion that avoids having to format the date and so should avoid the issue with the a.m., assuming that is what is causing the problem. I doubt the OP tried it though.
Make a querydef and save it:
Insert Into YourTable (YourDate, YourFK)
Values Forms!YourForm!YourDate, Forms!YourForm!YourFK;
Then run the querydef rather than an SQL String.
Assuming that Forms!YourForm!YourDate is bound to a date/time data type OR if unbound has a format property that indicates the control should hold a date, you don't need to format anything. The internal double precision number will be used so no formatting will get in the way.
Another solution assuming you insist on using embedded SQL Strings would include concatenating a string formatted as yyyy/mm/dd with a space followed by a string formated as Short Time could work since Short Time uses the 24 hour clock and so doesn't use the AM/PM designators.
dim strDate as String
strDate = Format(YourDate, "yyyy/mm/dd") & " " & Format(YourDate, "Short Time")
Then use strDate bracketed with #'s when building the SQL string.
No, the Debug.Print output in the VBA Immediate Window which is located below the code window, assuming it is active. Google "Access VBA Immediate Window" and "Access VBA Debug.Print". Here is one tutorial https://www.techonthenet.com/access/tutorials/vbadebug2010/debug07.php
Learn debugging techniques.
Append query creates a new record. You said you want to update existing record.
The string looks good so can you verify.
All table names are correct
All field names are correct
(double check all spelling)
Fecha_real is a date field
Event_ID is a numeric field
Status_ID is numeric
The string looks good so can you verify.
All table names are correct
All field names are correct
(double check all spelling)
Fecha_real is a date field
Event_ID is a numeric field
Status_ID is numeric
Computers are stupid. YOU have to get everything correct for them to understand you.
A human can spot the difference between evento_id and event_id, a computer cannot. Well not yet anyway. My google minis struggle between off and on
If you want to include the time you can. I use this function most of my time on my dates.
so just modify the update string and replace the format I provided with
SqlDate(me.fechaReal)
Code:
Function SQLDate(varDate As Variant) As String
'Purpose: Return a delimited string in the date format used natively by JET SQL.
'Argument: A date/time value.
'Note: Returns just the date format if the argument has no time component,
' or a date/time format if it does.
'Author: Allen Browne. allen@allenbrowne.com, June 2006.
If IsDate(varDate) Then
If DateValue(varDate) = varDate Then
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
Else
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
End If
End If
End Function
If you want to include the time you can. I use this function most of my time on my dates.
so just modify the update string and replace the format I provided with
SqlDate(me.fechaReal)
Code:
Function SQLDate(varDate As Variant) As String
'Purpose: Return a delimited string in the date format used natively by JET SQL.
'Argument: A date/time value.
'Note: Returns just the date format if the argument has no time component,
' or a date/time format if it does.
'Author: Allen Browne. allen@allenbrowne.com, June 2006.
If IsDate(varDate) Then
If DateValue(varDate) = varDate Then
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
Else
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
End If
End If
End Function
And what about inserting a new record on the table GC_Eventos using the last Event_ID created as the reference???
I cant related a previous Event_ID because there is no previous Event_ID. It is a totally new register. So, once you have filled out a form with the new data, when the user clicks on the button, it will create the new register on the first table (GC_Eventos) but at the same time, I would like to create a new register on another table (GC_Fecha) using that new unique ID (Evento_ID) that has been created few moments ago. All of this in the same event (Clicking the button) The second task comes after the first one is done.
Since I cannot use an Unique ID I was thinking of getting the last Unique ID (Evento_ID) using an aggregate function. (MAX) This would get me the last record created on the first table but i cannot use WHERE with aggregate functions and using HAVING might work but I don´t know.
It has not worked.
Using something like this:
Dim SQL as string
SQL = "INSERT INTO GC_Fecha (Evento_ID) SELECT (Evento_ID) FROM GC_Eventos WHERE Evento_ID = MAX(Evento_ID)"
CurrentDB.Execute SQL
But this doesn't work because of the WHERE clause. I tried using HAVING but it did not work as well.
Is it possible to do or do I need to create another independent event??
W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.