Generate Multiple Queries per Field value

[dssrun]

I have attempted to write VBA code that looks for all the distinct values in my department field and writes the number of queries per unique value as my criteria. However, I am not a pro coder and self taught myself VBA 6 months ago so I have attempted many times but I cannot figure it out. I am doing it this way because I need to export the different queries to one workbook but each has their own sheet. Thanks in advance!

    Dim mySQL As String
    Dim myRecord As Recordset
    Dim myValue as String
    Dim myDB as Database
    Dim myField as Integer

    Set myDB = Currentdb
    Set myField = 'How do I set it to my departments field?

    For Each myValue in myField
        'Find all the values in the department column (there are 6 unique  
         departments but thousands of records)
         mySQL = "SELECT * FROM tblDept WHERE" & myField = myVariable"
         DoCmd.RunSQL mySQL
         Set myRecord = myDB.OpenRecordSet mySQL
         'Do I need to save the recordset, if so how?
         DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,     
         myRecordSet, "My File Location",True
         Set myRecord = nothing
    Next

    Set myDB = nothing
    Set myField = nothing

End Function

[dssrun]

If anyone ever needs the solution I figured it out

Dim myRst As DAO.Recordset
Dim myRst2 As DAO.QueryDef
Dim mySQL As String
Dim mySQL2 As String
Dim myCriteria As String

mySQL = "SELECT DISTINCT Field1 FROM Table1"
Set myRst = CurrentDb.OpenRecordset(mySQL)

DoCmd.SetWarnings False

On Error Resume Next

Do While Not myRst.EOF
        myCriteria = myRst("Field1")
        mySQL2 = "SELECT * From Table1 WHERE Field1 =""" & myCriteria _
         & """"
         Set myRst2 = CurrentDb.CreateQueryDef(myCriteria, mySQL2)
         DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, myCriteria, "C:\My Documents\myfile.xls"
        Set myRst2 = Nothing
        myRst.MoveNext
Loop

Set myRst = Nothing

End Sub