for all that is good help!

[jzwp22]

What is the data type of tblStaff.Shift?

Are you calling this function from frmMain?

Is this the only form from which you will ever call the function?

 

[hamrthroer]

tblStaff.Shift is text

I'm calling this function from a command button on frmMain

I would like this module to be flexible so that it can use it in other databases with modification. But this is due tomorrow so whatever will work.

Thanks again

 

[jzwp22]

Since you have the SQL text within the function, it will only work for the current database. It sounds like you need the immediate, less flexible solution.

Since you are calling the function from the same form, you do not need to declare the ID in the function. So remove myID as Long from here:

Public Function ExportOvertime(myID As Long, strSheetName As String, strFilePath As String)

You also will not need the ID in the function call either, just the sheet name and path.

Once you make those changes, proceed with Bob's changes; they should get you to a solution. I have to go attend some meetings for the rest of the day...

 

[hamrthroer]

Bob I get

the select statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

 

[boblarson]

See if you can get it to give you a full SQL string by putting

Debug.Print strSQL

in the code just after the string is completely built. Then run the code and see if you get output in the Immediate Window. Copy that and paste it here so we can see the potential problem.

 

[hamrthroer]

Here is what I got in the immediate window (thanks I've never used that before).

SELECT tblStaff.StateEntryDate, tblStaff.LastName, tblStaff.FirstName, tblStaff.MI, tblStaff.PriPhone, FROM tblStaff WHERE ((((tblStaff.Shift)="8-4") AND ((tblStaff.Rank)='COI'OR (tblStaff.Rank)='COII'OR (tblStaff.Rank)='COS')AND ((tblStaff.WorkOT)=True))) ORDER BY tblStaff.StateEntryDate, tblStaff.Random;

 

[boblarson]

Okay, several issues if you look at it.

There are some spaces missing just before the OR's and AND and there is a comma after PriPhone which shouldn't be there.

 

[hamrthroer]

Thanks Bob! Finally!!!!!! I still need to manipulate the days off using a lable on the same form (frmMain) but I can role that out after this. Thank you all for your assistance. Any advice to make this less painfull?

 

[boblarson]

Any advice to make this less painfull?

Take Tylenol? But seriously, no I don't. It just can be a lot of tedious troubleshooting until you get to the end product.

 

[hamrthroer]

Ok. Almost done the export. I have to also add days off to the query. I have it done but for a missing operator. Here is the code, followed by the results in the immediate window.

        strSQL = "SELECT tblStaff.StateEntryDate, "
        strSQL = strSQL & "tblStaff.LastName, "
        strSQL = strSQL & "tblStaff.FirstName, "
        strSQL = strSQL & "tblStaff.MI, "
        strSQL = strSQL & "tblStaff.PriPhone, "
        strSQL = strSQL & "tblStaff.DaysOff "
        strSQL = strSQL & "FROM tblStaff "
        strSQL = strSQL & " WHERE ((((tblStaff.Shift)=" & Chr(34) & Forms!frmMain.cboRptShift & Chr(34) & ") "
        strSQL = strSQL & " AND ((tblStaff.DaysOff)=" & Chr(42) & Forms!frmMain.cboRptWeekDay & Chr(42) & ") "
        strSQL = strSQL & " AND ((tblStaff.Rank)='COI'"
        strSQL = strSQL & " OR (tblStaff.Rank)='COII'"
        strSQL = strSQL & " OR (tblStaff.Rank)='COS')"
        strSQL = strSQL & " AND ((tblStaff.WorkOT)=True))) "
        strSQL = strSQL & "ORDER BY tblStaff.StateEntryDate, "
        strSQL = strSQL & "tblStaff.Random;"

Immediate window results

SELECT tblStaff.StateEntryDate, tblStaff.LastName, tblStaff.FirstName, tblStaff.MI, tblStaff.PriPhone, tblStaff.DaysOff FROM tblStaff  WHERE ((((tblStaff.Shift)="8-4")  AND ((tblStaff.DaysOff)=*SUN*)  AND ((tblStaff.Rank)='COI' OR (tblStaff.Rank)='COII' OR (tblStaff.Rank)='COS') AND ((tblStaff.WorkOT)=True))) ORDER BY tblStaff.StateEntryDate, tblStaff.Random;

The immediate window showes the results (8-4 and *SUN*) that are on my form so I must be on the right track.

 

[boblarson]

Don't use Chr(42) around the days off. Use Chr(34) still. What is the value stored in the Days off field? I am scared to think but the way it is named implies that you have more than one stored in the same field for the same record. Is that true? Not a good method but we can overcome that by changing this:

strSQL = strSQL & " AND ((tblStaff.DaysOff)=" & Chr(42) & Forms!frmMain.cboRptWeekDay & Chr(42) & ") "

To this:

strSQL = strSQL & " AND ((tblStaff.DaysOff) Like '*" & Forms!frmMain.cboRptWeekDay & "*') "

 

[hamrthroer]

Sorry Bob! That fixed it right up! I was so excited I started readying for the implimentation.

Thank you all again for your assistance! I'm sure this won't be the last time.