Coding within ADO

[crowegreg]

I can't figure out why this isn't working. Within table PC_LOG, a text field is named [Request Number]. An example is FY14-099. What I'm trying to do is increase the numerial portion by 1. I first have to confirm I'm within the correct fiscal year, ie: FY14, then look for the MAX numeric part of [Request Number]. When I run the following code, it exits out of the sub when trying to do the msgbox rst.Fields(0)

Dim FiscalYear As Integer
Dim MaxRequestNumber As String
'Request Number is based on fiscal year, and a numeric count within that year
'Fiscal year is from July to June

'Establish correct year, and assign to variable FiscalYear
If DatePart("m", Date) > 6 Then
FiscalYear = Right(DatePart("yyyy", Date), 2) + 1
Else
FiscalYear = Right(DatePart("yyyy", Date), 2)
End If

'SELECT Max(Right([request number],3)) AS MaxNumber FROM PC_LOG WHERE (((PC_LOG.[Request Number]) Like "FY14*"))
strSQL = "SELECT Max(Right(PC_LOG.[Request Number],3)) AS MaxNumber FROM PC_LOG WHERE (((PC_LOG.[Request Number]) Like "
strSQL = strSQL & Chr(34) & "FY" & FiscalYear & "*" & Chr(34) & "))"



Set rst = New ADODB.Recordset
rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly

MsgBox rst.Fields(0)

rst.Close
Set rst = Nothing

I've confirmed the recordset only has 1 record. Thank you in advance!!

[ssanfu]

Add the blue line as shown and single step through the code.

'SELECT Max(Right([request number],3)) AS MaxNumber FROM PC_LOG WHERE (((PC_LOG.[Request Number]) Like "FY14*"))
strSQL = "SELECT Max(Right(PC_LOG.[Request Number],3)) AS MaxNumber FROM PC_LOG WHERE (((PC_LOG.[Request Number]) Like "
strSQL = strSQL & Chr(34) & "FY" & FiscalYear & "*" & Chr(34) & "))"

Debug.Print strSQL

Is the SQL formed correctly?
Can you copy the line from the immediate window, create a new query, paste in the SQL and execute the query?
Are the results what you expect?

[crowegreg]

I think I figured it out. Within my strSQL, I'm using MAX from the query wizard. I've been reading that MAX is not a function within VBA. What do you think?

[crowegreg]

You are correct. Their is a problem within the strSQL statement using the Like option. I can't figure out what I've typed incorrectly. Here's what I'm testing with.

strSQL = "SELECT PC_LOG.[Request Number] FROM PC_LOG WHERE (((PC_LOG.[Request Number]) Like "
strSQL = strSQL & Chr(34) & "FY14*" & Chr(34) & "))"

[crowegreg]

The problem was using the wildcard of "*" rather than "%" Everything worked fine after changing the wildcard. Thanks