String to Date

[Charles]

I have a piece of code that runs an append parameter query. When I run it as:

Set db = CurrentDb
Set qdf = db.QueryDefs("Pre-2008 Step4 app_Transactions")
qdf.Parameters(0) = #12/31/95#
qdf.Execute dbFailOnError

it works.

But if I try to make the date a string variable that I concatenate it
doesn't work. For example:

Set db = CurrentDb
Set qdf = db.QueryDefs("Pre-2008 Step4 app_Transactions")
ValYear = Left(Right(strMyArray(intI), 6), 4)
ValMonth = Right(strMyArray(intI), 2)
Select Case ValMonth
Case "01", "03", "05", "07", "08", "10", "12"
ValDays = "31"
Case "04", "06", "09", "11"
ValDays = "30"
Case "02"
ValDays = "28"
Case Else
GoTo Error_Handler:
End Select
If ValDays = "28" And ValYear = "2008" Then ValDays = "29"
ValDate = "#" & ValMonth & "/" & ValDays & "/" & ValYear & "#"
qdf.Parameters(0) = ValDate
qdf.Execute dbFailOnError

It errors out. It there someway I can make this work with the date as a
variable I can create from a string?

Thanks

 

[Dirk Goldgar]

I have a piece of code that runs an append parameter query. When I run it
as:

Set db = CurrentDb
Set qdf = db.QueryDefs("Pre-2008 Step4 app_Transactions")
qdf.Parameters(0) = #12/31/95#
qdf.Execute dbFailOnError

it works.

But if I try to make the date a string variable that I concatenate it
doesn't work. For example:

Set db = CurrentDb
Set qdf = db.QueryDefs("Pre-2008 Step4 app_Transactions")
ValYear = Left(Right(strMyArray(intI), 6), 4)
ValMonth = Right(strMyArray(intI), 2)
Select Case ValMonth
Case "01", "03", "05", "07", "08", "10", "12"
ValDays = "31"
Case "04", "06", "09", "11"
ValDays = "30"
Case "02"
ValDays = "28"
Case Else
GoTo Error_Handler:
End Select
If ValDays = "28" And ValYear = "2008" Then ValDays = "29"
ValDate = "#" & ValMonth & "/" & ValDays & "/" & ValYear & "#"
qdf.Parameters(0) = ValDate
qdf.Execute dbFailOnError

It errors out. It there someway I can make this work with the date as a
variable I can create from a string?

Try:

ValDate = ValMonth & "/" & ValDays & "/" & ValYear
qdf.Parameters(0) = CDate(ValDate)

 

[Beetle]

It looks like you are using that Select Case statement to deterimine the
last day of a given month. An easier way is to find the first day of the
following month, then subtract one day. You can then take the results
and use the DateSerial function to convert them. As an example;

Set db = CurrentDb
Set qdf = db.QueryDefs("Pre-2008 Step4 app_Transactions")
ValYear = Left(Right(strMyArray(intI), 6), 4)
ValMonth = Right(strMyArray(intI), 2)
ValDate = DateSerial(ValYear, ValMonth, Day(DateAdd("m", 1, _
& DateSerial(ValYear, ValMonth, 1))-1))
qdf.Parameters(0) = ValDate
qdf.Execute dbFailOnError

Then you wouldn't need the Select Case statement, or the extra coding
for leap year, etc.

 

[Dirk Goldgar]

It looks like you are using that Select Case statement to deterimine the
last day of a given month. An easier way is to find the first day of the
following month, then subtract one day. You can then take the results
and use the DateSerial function to convert them. As an example;

Set db = CurrentDb
Set qdf = db.QueryDefs("Pre-2008 Step4 app_Transactions")
ValYear = Left(Right(strMyArray(intI), 6), 4)
ValMonth = Right(strMyArray(intI), 2)
ValDate = DateSerial(ValYear, ValMonth, Day(DateAdd("m", 1, _
& DateSerial(ValYear, ValMonth, 1))-1))
qdf.Parameters(0) = ValDate
qdf.Execute dbFailOnError

Then you wouldn't need the Select Case statement, or the extra coding
for leap year, etc.

I didn't really read the code, but you're right, Beetle! And there's an
even easier way to get the last day of the month, because DateSerial accepts
0 as an argument:

Dim ValYear As Integer, ValMonth As Integer
Dim ValDate As Date

ValYear = Val(Left(Right(strMyArray(intI), 6), 4))
ValMonth = Val(Right(strMyArray(intI), 2))

ValDate = DateSerial(ValYear, ValMonth + 1, 0)

 

[Beetle]

Thanks for the tip Dirk. I didn't realize you could use 0 as an argument.

 

[Marshall Barton]

I have a piece of code that runs an append parameter query. When I run it as:

Set db = CurrentDb
Set qdf = db.QueryDefs("Pre-2008 Step4 app_Transactions")
qdf.Parameters(0) = #12/31/95#
qdf.Execute dbFailOnError

it works.

But if I try to make the date a string variable that I concatenate it
doesn't work. For example:

Set db = CurrentDb
Set qdf = db.QueryDefs("Pre-2008 Step4 app_Transactions")
ValYear = Left(Right(strMyArray(intI), 6), 4)
ValMonth = Right(strMyArray(intI), 2)
Select Case ValMonth
Case "01", "03", "05", "07", "08", "10", "12"
ValDays = "31"
Case "04", "06", "09", "11"
ValDays = "30"
Case "02"
ValDays = "28"
Case Else
GoTo Error_Handler:
End Select
If ValDays = "28" And ValYear = "2008" Then ValDays = "29"
ValDate = "#" & ValMonth & "/" & ValDays & "/" & ValYear & "#"
qdf.Parameters(0) = ValDate
qdf.Execute dbFailOnError

It errors out. It there someway I can make this work with the date as a
variable I can create from a string?

U sisn't really follow what you are starting with, but maybe
you can make use of some if these comments.

If you have a Text field or string variable that looks like
a date, you can use CDate to convert it to a real date.
This may be sufficient for setting the query parameter:

qdf.Parameters(0) = CDate(sDate)

You can use DateSerial to convert a date in parts to a real
date and/or get the date of the first or last day of the
month.

You can then use Format to convert a date to a string that
can be used to construct an SQL string.

If you have two variables with integer numbers for the month
and year, then the formatted date could be obtained using
something like:

I think this will do what you said your code is supposed to
do:

ValDate = Format(DateSerial(vYear, vMonth+1, 0),
"\#yyyy-m-d\#")

OTOH, I think a Date type parameter should be a date value,
not a string that looks like a date, so you probably should
not use Format.

The Month and Year functions might also be useful here?

 

[John W. Vinson]

Thanks for the tip Dirk. I didn't realize you could use 0 as an argument.

You can use any Integer as an argument; it's a clever function:

?dateserial(2008, 12, -3652)
12/1/1998

 

[Charles]

Thank you all very much for your advice. It was all very helpful!