Need help with variable

[Jay]

I need to copy all records with Test Dates for 2006 from a table to a Temp
table. My problem is the * and the strYear combination.



Dim strYear As String

strYear = InputBox("Which Year do you want as in 2006?")

DoCmd.RunSQL "INSERT INTO tblTemp (TestDate,)" & _
"SELECT tblDucatLists.ID, tblDucatLists.TestDate" & _
FROM tblDucatLists WHERE (tblDucatLists.TestDate) Like '*'" & strYear

 

[Pieter Wijnen]

try

DoCmd.RunSQL "INSERT INTO tblTemp (TestDate,)" & _
"SELECT tblDucatLists.ID, tblDucatLists.TestDate" & _
"FROM tblDucatLists WHERE (tblDucatLists.TestDate) Like '*" & strYear & "'"

HTH

pieter

 

[Guest]

Is the Testdate column of date/time data type? If so use the YEAR function
to return the year from each date as a number. Also, you seem to be missing
the ID column in the column list of the target table:

Dim strSQL As String
Dim strYear As String

strYear = InputBox("Which Year do you want as in 2006?")

strSQL = _
"INSERT INTO tblTemp (ID, TestDate,) " & _
"SELECT tblDucatLists.ID, tblDucatLists.TestDate " & _
"FROM tblDucatLists " & _
"WHERE YEAR(tblDucatLists.TestDate) = & strYear

DoCmd.RunSQL strSQL

BTW instead of the input box (cheap and cheerful, but crude) why not use a
combo box (either on the current form or on a separate dialogue form) with a
RowSource of:

SELECT DISTINCT YEAR(TestDate)
FROM tblDucatLists
ORDER BY YEAR(TestDate) DESC;

and execute the SQL statement in its AfterUpdate event procedure.

Ken Sheridan
Stafford, England

 

[Jay]

try

DoCmd.RunSQL "INSERT INTO tblTemp (TestDate,)" & _
"SELECT tblDucatLists.ID, tblDucatLists.TestDate" & _
"FROM tblDucatLists WHERE (tblDucatLists.TestDate) Like '*" & strYear &
"'"

HTH

pieter

The reply from pieter worked.

The ken reply failed. The format is Date/Time. It looks easier than
Pieter's. I spent some time on google and came up with Like '*'" &
strYear, but it failed to work. The original code was a little larger so
I broke it down smaller to make easier reading. I will play with the
combo box and use it, if I can get it to work. Should be simple.
Thanks to all.

So the solution was Like '*" & strYear & "'"

 

[Guest]

That suggests your TestDate column is of text data type, not date/time.
That's OK, but if you needed to do any date/time computations you'd need to
parse it and use the DateSerial or CDate function to return a true date/time
value.

Ken Sheridan
Stafford, England