VB6 sql query wont recognise all records in my ms Access Union Query

[aztrix]

im having a problem with pulling data from a union query from an access database. the field i am looking at is a Date field (Short Date) and im trying to get all records with dates between date one and date two (date picker control in vb6) im using kan ADO connection as follows

If frmReports.txtEmploymentLocation.Text = "ALL" Then
stSQL = "SELECT * FROM qryPersonnelAttachedRules_2withExclusions_Report0108 WHERE ExpiryDate > #" & dtpFrom.Value & "# AND ExpiryDate < #" & dtpTo.Value & "#" & stSQL2 & stSQL3 & ""
stlblLocationHeading = "All Branches"
Else
stSQL = "SELECT * FROM qryPersonnelAttachedRules_2withExclusions_Report0108 WHERE EmploymentLocation = '" & txtEmploymentLocation.Text & "' AND ExpiryDate > #" & dtpFrom.Value & "# AND ExpiryDate < #" & dtpTo.Value & "#" & stSQL2 & stSQL3 & ""
stlblLocationHeading = txtEmploymentLocation.Text
End If

MsgBox stSQL

rsReport0108.Open stSQL, modDatabaseConnection.cnFE, adOpenDynamic, adLockOptimistic

previously the data was being pulled from a standard select query (no union query involved) and it worked correctly, however i need to add extra data from another table so i had to go to a union query, and hence the report doesnt display and data.

any ideas?

 

[aztrix]

ok solved it, my union query reset my date field to text. worked that out by creating a 'create table' query from the union query and checking the field formats.