Loop Counter

[elementalnature]

Hello! I seem to be having an issue where I have a SQL/VBA script that calls dates from a table called tblForm91Entries and references a few other tables named tblPersonnel and tblform91MetaEntries. I'm using the below script to call this information on a report, that is based on a member inputting data on a form.

Option Compare Database
 
Function getItemDate(personnelID As Integer, Position As String, EntryTimeframe As String, EntryTimeframe1 As String)
    Dim mySQL, dateType As String, myDB As DAO.Database, myRec As DAO.Recordset
    'position = AEP SSO, LADO SSO, Evaluator, MCMDR, Instructor, AEP PSO, LADO SVO, AEP SVO
    'date Types = MRT , MRT, ERT, MRT, IRT, MRT, MRT, MRT
   
    If Position = "Evaluator" Then
        dateType = "ERT"
    ElseIf Position = "Instructor" Then
        dateType = "IRT"
    Else
        dateType = "MRT"
    End If
   
    mySQL = "SELECT tblPersonnel.LastName, tblPersonnel.PersonnelID, tblForm91Entries.ItemDate, tblForm91MetaEntries.EntryType, tblForm91MetaEntries.EntryTimeframe " + _
    "FROM tblForm91MetaEntries INNER JOIN (tblPersonnel INNER JOIN tblForm91Entries ON tblPersonnel.PersonnelID = tblForm91Entries.PersonnelID) ON tblForm91MetaEntries.ID = tblForm91Entries.MetaEntry " + _
    "WHERE (((tblPersonnel.PersonnelID)=" + Nz(Str(personnelID)) + ") AND ((tblForm91MetaEntries.EntryType)='" + dateType + "') AND ((tblForm91MetaEntries.EntryTimeframe)='" + Nz(EntryTimeframe) + "')); "
   
    Set myDB = CurrentDb
    Set myRec = myDB.OpenRecordset(mySQL)
   
    Do While Not myRec.EOF
        getItemDate = Nz(myRec![ItemDate])
        myRec.MoveNext
    Loop
    If Nz(getItemDate) = "" Then
        getItemDate = ""
    End If
End Function

The two dialog boxes are called via

Function getItemDate(personnelID As Integer, Position As String, EntryTimeframe As String, EntryTimeframe1 As String)

(note that the blue text are the expressions that act as dialog boxes.)

So within the tblform91MetaEntries are the values that I want to input into the dialog boxes, usually it is something similar to a month year such as August 2012 or a quarter value such as 2nd Quarter 2012. For my purpose I need two dialog boxes that allow me to enter both values (August 2012 and 2nd Quarter 2012) however, the VBscript only uses the first inputted value. For example if August 2012 is entered in the first box, it completely disregards 2nd Quarter 2012 and visa versa. I was told to loop it so it asks the questions until it runs out of records to loop to. Any ideas?

[June7]

Use debug techniques, refer to link at bottom of my post. Set a breakpoint, follow the code as it executes one line at a time. Does it do what you expect?

Have you compiled the code? Does the procedure run?

You probably need the If structure within the loop. Consider:

Do While Not myRec.EOF
   If Nz(getItemDate) <> "" Then
        getItemDate = myRec![ItemDate]
   End If
   myRec.MoveNext
Loop

Is it possible the sql will have more than one record that will meet the If condition? What is it you are trying to accomplish? You might be making this harder than necessary. Could the sql be designed with criteria to return only one record?

[elementalnature]

The code compiles, runs, returns the correct records for the first value input. The second value however is completely disregarded. So if I add an if statement, that might help?

[pbaldy]

You say the second value is disregarded, but I don't see that you ever use it in the code. Am I missing something?