Please Help! Between Date Filter (1 Viewer)

[lovett10]

Private Sub Command55_Click()
Dim strFilter As String
Dim VarItem As Variant
Dim DTpicker6 As Date
Dim DTPicker7 As Date
'If Quote Number is blank
If IsNull(Me.Combo41) Then
strFilter = "[Engineer 1] = '" & Me!Combo50 & "'Or " & _
"[Engineer 2] = '" & Me!Combo50 & "'Or " & _
"[Engineer 3] = '" & Me!Combo50 & "'"
DoCmd.OpenReport "VisitSheetTableReport", acPreview, , strFilter
End If

'If Engineer is blank
If IsNull(Me.Combo50) Then
strFilter = "[Quote Number] = '" & Me!Combo41 & "'"
DoCmd.OpenReport "VisitSheetTableReport", acPreview, , strFilter
End If

'If All are filled
If Me.Combo41 & "" <> "" And Me.Combo50 & "" <> "" And Me.DTpicker6 & "" <> "" And Me.DTPicker7 & "" <> "" Then
strFilter = "[Date] between " & Me.DTpicker6 & " and " & Me.DTPicker7
DoCmd.OpenReport "VisitSheetTableReport", acPreview, , strFilter
End If
End Sub

The First two sections work its just the between dates that dont. DTPicker7 is the date start and DTpicker6 is date finish

Incase you cant tell, im trying to make the form create a open a report and filter between the QuoteNo, Engineer Name and Between dates. The dates is the one i am struggling with


Thanks in advanced and any help would be much appreciated.

 

[jdraw]

Just a suggestion to help with debugging.

Comment out your Docmd.openreport..... lines

Insert a line for testing (you could put it above each of the Docmd.openreports)
Debug.print strFilter

This will show you what is in strFilter and you can isolate the error.

Then show us what results.

 

[lovett10]

Just a suggestion to help with debugging.

Comment out your Docmd.openreport..... lines

Insert a line for testing (you could put it above each of the Docmd.openreports)
Debug.print strFilter

This will show you what is in strFilter and you can isolate the error.

Then show us what results.

I dont know what you mean, when i comment out the DoCmd.openReport when i run nothing happens... anyway

Ive been working on it and i have this...

'If All are filled
If Me.Combo41 & "" <> "" And Me.Combo50 & "" <> "" And Me.DateStart & "" <> "" And Me.DateFinish & "" <> "" Then
strFilter = "[DateField] BETWEEN " & Me.DateStart & " AND " & Me.DateFinish
DoCmd.OpenReport "VisitSheetTableReport", acPreview, , strFilter
End If
End Sub

Except when i run it asks me to type in parameter for DateField, maybe im derping but what am i supposed to put or is this completely wrong...

Help!! (Much appreciate the help btw )

 

[jdraw]

Do you know about the immediate window?
You can write messages to that window using debug.print

What version of access do you have?

What are the properties of Me.DTpicker6 and Me.DTpicker7

When you say nothing happens.... what exactly did you try? Did you put in the debug.print strfilter ?

 

[lovett10]

Do you know about the immediate window?
You can write messages to that window using debug.print

What version of access do you have?

What are the properties of Me.DTpicker6 and Me.DTpicker7

When you say nothing happens.... what exactly did you try? Did you put in the debug.print strfilter ?

Hi yes i see the immediate Window Now...

I have access 2010 and im sorry for changing it about but i decided to instead of using DTpickers ive used two text boxes... "DateStart" and "DateFinish"

Heres is my code..

'If All are filled
If Me.Combo41 & "" <> "" And Me.Combo50 & "" <> "" And Me.DateStart & "" <> "" And Me.DateFinish & "" <> "" Then
strFilter = "[Date Of Work] BETWEEN " & Me.DateStart & " AND " & Me.DateFinish
DoCmd.OpenReport "VisitSheetTableReport", acPreview, , strFilter
Debug.Print strFilter
End If
End Sub

My immediate window says... [Date Of Work] BETWEEN 30/11/2011 AND 03/12/2011

But although there are fields in between them dates it shows nothing.

Thanks for the help and sorry for changing things i was trying to fix it incase no1 replied. ill stop messing about with it now

 

[jdraw]

I have acc2003 with the calendar control.

If you want to use a combo box set up that looks like a calendar
see http://www.fontstuff.com/access/acctut13a.htm

I see posts saying there were issues with 2010 -calendar control deprecated

Also this one for alternatives
http://msdn.microsoft.com/en-us/library/gg251104.aspx

Just looked again at your filter. You may need to adjust to

strFilter = "[Date Of Work] BETWEEN #" & Me.DateStart & "# AND #" & Me.DateFinish & "#"

 

[lovett10]

Just looked again at your filter. You may need to adjust to

strFilter = "[Date Of Work] BETWEEN #" & Me.DateStart & "# AND #" & Me.DateFinish & "#"

Just did a quick check and i think the changes are working, thanks alot for the help

 

[lovett10]

I have acc2003 with the calendar control.

If you want to use a combo box set up that looks like a calendar
see http://www.fontstuff.com/access/acctut13a.htm

I see posts saying there were issues with 2010 -calendar control deprecated

Also this one for alternatives
http://msdn.microsoft.com/en-us/library/gg251104.aspx

Just looked again at your filter. You may need to adjust to

strFilter = "[Date Of Work] BETWEEN #" & Me.DateStart & "# AND #" & Me.DateFinish & "#"

Im now having a problem with combining the between date:

strFilter = "[Date Of Work] BETWEEN [COLOR=red]#[/COLOR]" & Me.DateStart & "[COLOR=red]#[/COLOR] AND [COLOR=red]#[/COLOR]" & Me.DateFinish & "[COLOR=red]#[/COLOR]"

and engineer: CODE]strFilter = "[Engineer 1] = '" & Me!Combo50 & "'" [/CODE]

I have

strFilter = "[Engineer 1] = '" & Me!Combo50 & "'And " & _
"[Date Of Work] BETWEEN #" & Me.DateStart & "# AND #" & Me.DateFinish & "#"
DoCmd.OpenReport "VisitSheetTableReport", acPreview, , strFilter

I wont be able to see any responses till tommorow as im now heading home but thanks for any additional help

 

[jdraw]

This may need a space between 'And
strFilter = "[Engineer 1] = '" & Me!Combo50 & "' And " & _
"[Date Of Work] BETWEEN #" & Me.DateStart & "# AND #" & Me.DateFinish & "#"
DoCmd.OpenReport "VisitSheetTableReport", acPreview, , strFilter

 

[Lister]

Watch out for an Engineer named "O'Conner" (for example) a ' in the name will stuff up your code.
User strFilter = "[Engineer1] = " & chr(34) & me!combo50 & chr(34) & " And " ....

This should stop this error happening within string values. Good luck mate.

 

[lovett10]

If Me.Combo41 & "" <> "" And Me.Combo50 & "" <> "" And Me.DateStart & "" <> "" And Me.DateFinish & "" <> "" Then
strFilter = "[Engineer 1] = '" & Chr(34) & Me!Combo50 & Chr(34) & "' And " & _
"[Date Of Work] BETWEEN #" & Me.DateStart & "# AND #" & Me.DateFinish & "#"
DoCmd.OpenReport "VisitSheetTableReport", acPreview, , strFilter
Debug.Print strFilter
End If
End Sub

I now have this and i get this in the immediate window:
[Engineer 1] = '"Andrew Leggett"' And [Date Of Work] BETWEEN #01/10/2011# AND #31/12/2011#

The report opens with no errors, but doesnt show any feilds its just a blank report.

Appreciate any help and thanks for it so far

 

[spikepl]

Now read #10 again, and amend the code as #10 outlined, not as you have done.

 

[lovett10]

Ahh yes thanks, just one thing ive noticed... any way of making it open in report view as apose to print preview?

 

[spikepl]

Lookup OpenReport in the documentation and see what parameters there are and what they do.

 

[lovett10]

Ive tried acViewReport and i get Run-time error '3075' it says Syntax error in string in query expression

 

[lovett10]

nevermind there was a misstype in the code its working now thanks everyone

 

[lovett10]

Im getting some weird results
In the code below

'If Engineer is blank, rest filled
If IsNull(Me.Combo50) And Me.Combo41 & "" <> "" And Me.DateStart & "" <> "" And Me.DateFinish & "" <> "" Then
strFilter = "[Quote Number] = " & Chr(34) & Me!Combo41 & Chr(34) & " And " & _
"[Date Of Work] BETWEEN #" & Me.DateStart & "# AND #" & Me.DateFinish & "#"
DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
Debug.Print strFilter
End If

The immediate is [Quote Number] = "Q21938" And [Date Of Work] BETWEEN #28/12/2011# AND #04/01/2012#

Which looks correct however in this example it is showing entries from the 05/01/2012, however when i search between 04/01/2012 and 06/01/2012 it doesnt show the 05/01/2012 entries??? any1 got any ideas?

 

[lovett10]

Lookup OpenReport in the documentation and see what parameters there are and what they do.

'If Quote Number is blank, rest filled
If IsNull(Me.Combo41) And Me.Combo50 & "" <> "" And Me.DateStart & "" <> "" And Me.DateFinish & "" <> "" Then
strFilter = "[Engineer 1] = " & Chr(34) & Me!Combo50 & Chr(34) & " And " & _
"[Date Of Work] BETWEEN #" & Me.DateStart & "# AND #" & Me.DateFinish & "#" & " Or " & _
"[Engineer 2] = " & Chr(34) & Me!Combo50 & Chr(34) & " And " & _
"[Date Of Work] BETWEEN #" & Me.DateStart & "# AND #" & Me.DateFinish & "#" & " Or " & _
"[Engineer 3] = " & Chr(34) & Me!Combo50 & Chr(34) & " And " & _
"[Date Of Work] BETWEEN #" & Me.DateStart & "# AND #" & Me.DateFinish & "#"
DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
End If

Thats my code for this section and its something wrong with linking the date between with the next part. at the minute its only showing for engineer 1 not 2 and 3.

the reason i have engineer 1 and date or engineer 2 and date and so on is because otherwise it would do (engineer 1) or (engineer 2) or (engineer 3 and date)

 

[lovett10]

Im getting some weird results
In the code below

'If Engineer is blank, rest filled
If IsNull(Me.Combo50) And Me.Combo41 & "" <> "" And Me.DateStart & "" <> "" And Me.DateFinish & "" <> "" Then
strFilter = "[Quote Number] = " & Chr(34) & Me!Combo41 & Chr(34) & " And " & _
"[Date Of Work] BETWEEN #" & Me.DateStart & "# AND #" & Me.DateFinish & "#"
DoCmd.OpenReport "VisitSheetTableReport", acViewReport, , strFilter
Debug.Print strFilter
End If

The immediate is [Quote Number] = "Q21938" And [Date Of Work] BETWEEN #28/12/2011# AND #04/01/2012#

Which looks correct however in this example it is showing entries from the 05/01/2012, however when i search between 04/01/2012 and 06/01/2012 it doesnt show the 05/01/2012 entries??? any1 got any ideas?

It seems that 05/01/2012 first shows when i search between 01/11/2012 and 11/11/2012 maybe the between takes some sort of average... please help

 

[lovett10]

I think ive found the problem and solution i think its to do with the date confusing itself between english and us times.

heres what i did

Change your WhereCondition to "Start_Date between fVBDate(#" & Me.StartDate & "#) and fVBDate(#" & Me.EndDate & "#)"

Put this in a module:

Function fVBDate(datSQL as date)

if isnull(datSQL) then _
Exit Function

if CInt(Mid(datSQL, 1, 2)) <= 12 then

fVBDate = (Mid([datSQL], 4, 2) & "/" & Mid([datSQL], 1, 2) & "/" & Mid([datSQL], 7)

else

fVBDate = datSQL

end if

End Function


Got this from another forum so credit to them for that just wanted to let anyone searching for the same problem know