number and date field

[enrico via AccessMonster.com]

what is the proper way in saving a number and date field using VBA? if my
textbox with a number and date field is called txtnumber and txtStartdate
respectively, how can i state it in an INSERT statement? this is my sample
anyway, i don't know if it's correct:

INSERT INTO table(Age, Startdate)
VALUES (& me.txtnumber &, '" & me.txtStartdate & "')

 

[John W. Vinson]

what is the proper way in saving a number and date field using VBA? if my
textbox with a number and date field is called txtnumber and txtStartdate
respectively, how can i state it in an INSERT statement? this is my sample
anyway, i don't know if it's correct:

INSERT INTO table(Age, Startdate)
VALUES (& me.txtnumber &, '" & me.txtStartdate & "')

A Date field must be delimited by # characters, not '; and you cannot refer to
the me. shortcut in a Query, since the Query has no way to know what form is
calling it.

If you're building the query in VBA, build it as a text string:

Dim strSQL As String
strSQL = "INSERT INTO table(Age, Startdate) VALUES (" _
& me.txtnumber & ", #" & me.txtStartdate & "#);"

This will assemble the pieces:

"INSERT INTO table(Age, Startdate) VALUES ("
123 <or whatever number is in txtnumber>
", #"
2/18/2009
"#);"

to build the valid SQL statement

INSERT INTO table(Age, Startdate) VALUES (123, #2/18/2009#);

You would then use the RunSql or (better) the Execute method to actually
execute the query.

 

[Tom van Stiphout]

On Tue, 17 Feb 2009 23:03:43 -0700, John W. Vinson

What John said is correct, or if you want to use control references,
you can create the query with absolute form references rather than
"Me":
INSERT INTO table(Age, Startdate)
VALUES (Forms!myForm!txtnumber, Forms!myForm!txtStartdate)

-Tom.
Microsoft Access MVP

 

[enrico via AccessMonster.com]

i tried the sample code you gave me and used it in my VBA code, it gives an
error like this:

Syntax error in date in query expression '# #'.

by the way, i used a short date format on my data field in both my table and
textbox.

 

[John W. Vinson]

i tried the sample code you gave me and used it in my VBA code, it gives an
error like this:

Syntax error in date in query expression '# #'.

by the way, i used a short date format on my data field in both my table and
textbox.

That suggests that you're trying to insert a NULL date. What do you want
inserted if the form textbox is blank?

One possible problem with using the Short Date format is that it is sensitive
to the computer's Regional settings. Literal dates in an insert must be in
either mm/dd/yyyy format, or an unambiguous format such as yyyy-mm-dd. If your
computer is set to define a short date as dd/mm/yyyy (as in many countries)
you'll get incorrect data - the Insert ignores the computer's default date
format.

You may want to use NZ() to define a date for a blank form control, and use

& Format([txtStartDate], "\#mm\/dd\/yyyy\#")

in your insert statement.

 

[enrico via AccessMonster.com]

pardon me if i'm not so familiar with the codes but where will i put NZ() on
the code you gave me?

 

[John W. Vinson]

pardon me if i'm not so familiar with the codes but where will i put NZ() on
the code you gave me?

Sorry... should have included the whole thing! Try

strSQL = "INSERT INTO table(Age, Startdate) VALUES (" _
& me.txtnumber & ", " & IIF(IsNull(me.txtStartdate, NULL,
"\#mm\/dd\/yyyy\#") & ");"

 

[enrico via AccessMonster.com]

thanks a lot. a already tried the code you gave me, but there's a slight
problem there's a compile error. it states:

Compile error.
Expected: )

 

[enrico via AccessMonster.com]

don't mind my last post, i just missed a something. thanks a lot!