Can someone help me with my sql in VBA please?

[shabbaranks]

Hi,

Ive added breaks and the variables are returning their values fine when I hover over them, but can I see where the syntax error is - can I.....

 strSQL = "SELECT TimesheetTable.sUser, TimesheetTable.Activity, TimesheetTable.Hours, TimesheetTable.Project, TimesheetTable.[Task Date], TimesheetTable.Description " & vbCrLf & _
    "FROM TimesheetTable " & vbCrLf & _
    "WHERE (((TimesheetTable.sUser)=""" & strEmployeeName & """) AND ((TimesheetTable.Project)=""" & strProject & """) AND ((TimesheetTable.[Task Date])>=#" & frDate & "# And <=#" & toDate & "#));"

Friday points for the first to spot it as I cant seem to

 

[namliam]

 ... [COLOR="Red"]AND ((TimesheetTable.[Task Date])>=#" & frDate & "# And <=#" & toDate & "#)[/COLOR]);"

Either Field Between x and y
or Field >= x and field <= y

Please come bring my friday points to Leidseplein, amsterdam by 6pm...

Edit, just to make sure, you are ware that frDate and toDate need to be in US Format as well? You may want to use Format(Yourfield,"MM\/DD\/YYYY") to ensure it is

 

[shabbaranks]

Either Field Between x and y
or Field >= x and field <= y

Please come bring my friday points to Leidseplein, amsterdam by 6pm...

Edit, just to make sure, you are ware that frDate and toDate need to be in US Format as well? You may want to use Format(Yourfield,"MM\/DD\/YYYY") to ensure it is

Do you mean like this?

 strSQL = "SELECT TimesheetTable.sUser, TimesheetTable.Activity, TimesheetTable.Hours, TimesheetTable.Project, TimesheetTable.[Task Date], TimesheetTable.Description " & vbCrLf & _
    "FROM TimesheetTable " & vbCrLf & _
    "WHERE (((TimesheetTable.sUser)=""" & strEmployeeName & """) AND ((TimesheetTable.Project)=""" & strProject & """) AND ((TimesheetTable.[Task Date])>=#" & frDate & "#) And ((TimesheetTable.[Task Date])<=#" & toDate & "#);"

I hate date fields in Access\VBA just cant get my head around it

 

[pr2-eugin]

Try.

strSQL = "SELECT TimesheetTable.sUser, TimesheetTable.Activity, TimesheetTable.Hours, TimesheetTable.Project, " & _
         "TimesheetTable.[Task Date], TimesheetTable.Description " & _
         "FROM TimesheetTable WHERE ((TimesheetTable.sUser = " & Chr(34) & strEmployeeName & Chr(34) & ") AND " & _
         "(TimesheetTable.Project = " & Chr(34) & strProject & Chr(34) & ") AND " & _
         "(TimesheetTable.[Task Date] [COLOR=Red][B]BETWEEN [/B][/COLOR]" & [COLOR=Red][B]Format([/B][/COLOR]frDate,[COLOR=Red][B] "\#mm\/dd\/yyyy\#"[/B][B])[/B][/COLOR] & " And " & [COLOR=Red][B]Format([/B][/COLOR]toDate[COLOR=Red][B], "\#mm\/dd\/yyyy\#")[/B][/COLOR] & "));"

 

[shabbaranks]

Try.

strSQL = "SELECT TimesheetTable.sUser, TimesheetTable.Activity, TimesheetTable.Hours, TimesheetTable.Project, " & _
         "TimesheetTable.[Task Date], TimesheetTable.Description " & _
         "FROM TimesheetTable WHERE ((TimesheetTable.sUser = " & Chr(34) & strEmployeeName & Chr(34) & ") AND " & _
         "(TimesheetTable.Project = " & Chr(34) & strProject & Chr(34) & ") AND " & _
         "(TimesheetTable.[Task Date] [COLOR=red][B]BETWEEN [/B][/COLOR]" & [COLOR=red][B]Format([/B][/COLOR]frDate,[COLOR=red][B] "\#mm\/dd\/yyyy\#"[/B][B])[/B][/COLOR] & " And " & [COLOR=red][B]Format([/B][/COLOR]toDate[COLOR=red][B], "\#mm\/dd\/yyyy\#")[/B][/COLOR] & "));"

Thanks - will give it a go. Before I do, I used this method to set the dates

   frDate = CDate(Me.TotalHours_Fromtxtbox)
   toDate = CDate(Me.TotalHours_Totxtbox)

Is this not the correct way to do it?

Thanks again

 

[pr2-eugin]

Still using Dates in VBA is different. Even if you use CDate or DateSerial functions, JET engine requires the Dates to be in American format like Month/Date/Year. So it is always best to wrap it in the format.

 

[shabbaranks]

Ok, but... And theres always a but

Am I correct in thinking if whilst debugging and I hover over the date variable in the sql and it shows correctly then the criteria is in fact correct? For example frDate on the form was 27/01/2013 and this was also the case when debugging.

 

[pr2-eugin]

Okay it is Friday ! I will try to pull myself together.

VBA compiler users your Regional settings, Access JET engine uses American Date format.

The date you currently have 27/01/2013 since there is no 27 month number JET engine is clever enough to understand that is the Date and not month. So it will flip it around when passing it to the Engine. But if you pass a date 05/01/2014, the compiler will assume it as 5th January 2014, but the JET engine will think it as 1st May 2014. See the difference? JET engine assumes that you have passed the right date if it has no problem trying to flip it around.

Not all that glitters are Gold. So what you see might not what the DB understands. To fool proof, we use the Format function. Or my personal favorite use CDbl to cast the Date to Double number which is what the Dates are stored as inside the tables.

 

[shabbaranks]

Thanks, I understand. TO elaborate on CDbl are you setting it to this as per

frDate = CDbl(Me.TotalHours_Fromtxtbox)
   toDate = CDbl(Me.TotalHours_Totxtbox)

As if there's an easier way to handle dates Im always up for learning

 

[pr2-eugin]

Check my blog post on Playing with Dates in Access !

 

[shabbaranks]

Check my blog post on Playing with Dates in Access !

Im actually glad to know its not just me who doesn't get on with dates in VBA

So although the dates appear to be doubles, the fact that dates are stored like this anyway Access sees them correctly? Im about to give up on this, Im sure its something really trivial but my query seems correct yet my list box isn't populating

   If Me.TotalHours_Combo.Column(2) = strEmployeeName And UserGroup = 2 Then
   
   frDate = CDbl(Me.TotalHours_Fromtxtbox)
   toDate = CDbl(Me.TotalHours_Totxtbox)
   strProject = Me.ProjectRef_txtbox
   
    strSQL = "SELECT TimesheetTable.sUser, TimesheetTable.Activity, TimesheetTable.Hours, TimesheetTable.Project, TimesheetTable.[Task Date], TimesheetTable.Description " & vbCrLf & _
    "FROM TimesheetTable " & vbCrLf & _
    "WHERE (((TimesheetTable.sUser)=""" & strEmployeeName & """) AND ((TimesheetTable.Project)=""" & strProject & """) AND ((TimesheetTable.[Task Date])>=" & frDate & " And (TimesheetTable.[Task Date])<=" & toDate & "));"
    
    Me.TotalHrs_Listbox.RowSourceType = "table/query"
    Me.TotalHrs_Listbox.ColumnCount = "7"
    Me.TotalHrs_Listbox.ColumnWidths = "2cm;2cm;2cm;2cm;2cm;2cm"
    Me.TotalHrs_Listbox.RowSource = strSQL
    Me.TotalHrs_Listbox.Requery
    
    Else
    MsgBox "Please Select Your Name"
    Me.TotalHours_Combo.SetFocus
    
    End If
    End Sub

 

[pr2-eugin]

LOL ! Is there any reason why you are determined not to try the suggestion in Post #4?

Please tell me you have changed the declaration of frDate and toDate as Double and not Dates still.

Have you tried Debugging? It is the most important weapon a programmer can use against all of this bugs.

 

[shabbaranks]

LOL ! Is there any reason why you are determined not to try the suggestion in Post #4?

Please tell me you have changed the declaration of frDate and toDate as Double and not Dates still.

Have you tried Debugging? It is the most important weapon a programmer can use against all of this bugs.

I tried the suggestion in #4 and I still get an empty listbox. I have been using the debugging tools (well as well as I know how to use them) and I cant for the life of me see why the list box isn't showing the results.

If I use the immediate window to show the form values they are correct, if I then add the sql query to it to show me if its passing the correct values - it is. The only thing that's not happening is Im not getting the values into the list box.

And yes I did change the values from date to double

 

[namliam]

The only way to see the ACTUAL sql is like this:

    strSQL = "SELECT TimesheetTable.sUser, TimesheetTable.Activity, TimesheetTable.Hours, TimesheetTable.Project, TimesheetTable.[Task Date], TimesheetTable.Description " & vbCrLf & _
    "FROM TimesheetTable " & vbCrLf & _
    "WHERE (((TimesheetTable.sUser)=""" & strEmployeeName & """) AND ((TimesheetTable.Project)=""" & strProject & """) AND ((TimesheetTable.[Task Date])>=" & frDate & " And (TimesheetTable.[Task Date])<=" & toDate & "));"
[COLOR="Red"]    debug.print strSQL[/COLOR]
    Me.TotalHrs_Listbox.RowSourceType = "table/query"

You can then take the SQL from the immediate window and paste it into an actual query and see its results in the designer.

My guess is that even with whatever you do, the way you enter your date into your textboxes is actually the root of the cause....
Try ensuring the date you enter is actually a US Date format to start with....

And please post relevent parts of the code as well, the way that the variables of frdate and todate are "DIM-ed" as can really matter a lot!

 

[shabbaranks]

Hi Guys,

I got this working in the end using the form values rather than converting the form values into vba code.

What I'm now trying to do is use a wild card within an if statement. So if tick box is true then use the form value else use a wild card search (which should return all values)

If Me.AllProjects_TB = False Then
strProjectRef = Me.ProjectRef_txtbox
Else
strProjectRef = "Like * """
End If

Am I even close? Thanks

 

[pr2-eugin]

Am I even close? Thanks

Don't think so.. Like operator does not work the way you have shown it as..

SELECT something FROM somewhere
WHERE someField = "Alpha";

When you use Like you will use it as,

SELECT something FROM somewhere
WHERE someField LIKE "Alpha";
'Or
SELECT something FROM somewhere
 WHERE someField LIKE "A*";

But what you are trying to do is..

SELECT something FROM somewhere
 WHERE someField = LIKE "A*";

Which will not work. You have to construct the SQL Query or have the SQL as shown above permanently use a LIKE instead of Equals (=)

 

[shabbaranks]

Fair enough - it was me just being lazy. Ive put some logic into my code and created 2 sql queries depending on the selection