String SQL statement not returning value

[Todd H]

Dim dbs As Database
Dim strCurPer As String
Dim intCurPer As Integer
Set dbs = CurrentDb
strCurPer = "SELECT BillingPeriodNumber FROM tblBillingPeriod" &
"WHERE [tblBillingPeriod.BillingPeriodEndDate] Between" & "#" &
Format(DateAdd("m", -4, Date), "mm/dd/yyyy") & "# And #" _
& Format(DateAdd("m", -3, Date), "mm/dd/yyyy") & "#"
'MsgBox to Troubleshoot
MsgBox ("strCurPer is" & [strCurPer])
intCurPer = Val(strCurPer)

I am trying to have this SQL statement return a BillingPeriodNumber
and then turn that String value into an Integer. I added the text box
to see the value of strCurPer and now it returns the text of the code
with the dates calculated. What am I doing wrong?

 

[Marshall Barton]

Dim dbs As Database
Dim strCurPer As String
Dim intCurPer As Integer
Set dbs = CurrentDb
strCurPer = "SELECT BillingPeriodNumber FROM tblBillingPeriod" &
"WHERE [tblBillingPeriod.BillingPeriodEndDate] Between" & "#" &
Format(DateAdd("m", -4, Date), "mm/dd/yyyy") & "# And #" _
& Format(DateAdd("m", -3, Date), "mm/dd/yyyy") & "#"
'MsgBox to Troubleshoot
MsgBox ("strCurPer is" & [strCurPer])
intCurPer = Val(strCurPer)

I am trying to have this SQL statement return a BillingPeriodNumber
and then turn that String value into an Integer. I added the text box
to see the value of strCurPer and now it returns the text of the code
with the dates calculated. What am I doing wrong?

That string expression needs a space before and after all
the keywords.

You would need to open a recordset on the query to retrieve
the value.

However, all you need is to use the DLookup function to get
the value:

intCurPer = DLookup("BillingPeriodNumber" _
, "tblBillingPeriod" _
, "BillingPeriodEndDate Between " _
& Format(DateAdd("m", -4, Date), "\#m\/d\/yyyy\#") _
& " And " _
& Format(DateAdd("m", -3, Date), "\#m\/d\/yyyy\#")

 

[Marshall Barton]

Todd H wrote:

[snip unneeded code]

I am trying to have this SQL statement return a BillingPeriodNumber
and then turn that String value into an Integer. I added the text box
to see the value of strCurPer and now it returns the text of the code
with the dates calculated. What am I doing wrong?

After using the DLookup I suggested in your other thread:

WIth Me.Recordset
If .RecordCount > 0 Then
.FindFirst "BillingPeriodNumber = " & intCurPer
End If
End With

 

[Todd H]

Todd H wrote:

[snip unneeded code]

I am trying to have this SQL statement return a BillingPeriodNumber
and then turn that String value into an Integer. I added the text box
to see the value of strCurPer and now it returns the text of the code
with the dates calculated. What am I doing wrong?

After using the DLookup I suggested in your other thread:

WIth Me.Recordset
If .RecordCount > 0 Then
.FindFirst "BillingPeriodNumber = " & intCurPer
End If
End With

worked like a champ! Many Thanks!