Cant start report

[paul1707]

Hi All,

I am wondering if my syntax is right for this. I haven't been using access for long as you will no doubt find.

I have a form which i have a cbo box to select a contractor lastname so i can get the jobs due for starting on a scheduled date.

Form has a Combo6 which is a lookup via a query.
It has a txtFrom unbound text box for the date.

DoCmd.OpenReport "rptAllJobs", acViewPreview, , "[InstLastName]=" & Me.Combo6, "[SchedInstallDate]=#" & Me.txtFrom & "#"


I keep looking at this and can't see where i'm going wrong.

Any ideas?

Thank you in advance.

 

[boblarson]

Two things -

1. If the combo returns text you need quotes.

2. You don't separate the criteria with a comma

DoCmd.OpenReport "rptAllJobs", acViewPreview, , "[InstLastName]=[color=red][b]'[/b][/color]" & Me.Combo6 [color=red][b]& "' And [/b][/color][SchedInstallDate]=#" & Me.txtFrom & "#"

 

[paul1707]

Thank you very much Bob, i admire your knowledge.

Thank you again!

 

[paul1707]

Bob could you have a look at this and let me know if it's right for a date range after a lastname selection. It is returning jobs outside the date range specified.

DoCmd.OpenReport "rptAllJobs", acViewPreview, , "[InstLastName]='" & Me.Combo6 & "' And [SchedInstallDate] Between #" & Me.txtFrom & "# And #" & Me.txtTo & "#"


Thanks again.

 

[boblarson]

It looks right. Can you post the db?

See here for how:
http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[boblarson]

Okay - got it - Change this:

strField = "SchedInstallDate"

to this:

strField = "[SchedInstallDate]"

 

[paul1707]

That section ok.

Okay - got it - Change this:

strField = "SchedInstallDate"

to this:

strField = "[SchedInstallDate]"

Hi Bob,

Thanks for looking over that. The one you are looking at seems to be working ok, i will do some further testing when i get some more data in it.

The error is in the frmInstSelJob date range selector.

Thanks again.

Paul1707

 

[boblarson]

Can you give me a specific name and date range to look at? I just took a look and it appears to work fine for me. The data has some missing info though so that could be causing the problem.

 

[paul1707]

Hi Bob,

It does appear to be working, i will do some more checking on it when i put in some more data.

I have a head full of flu so i'm probably seeing things.....

I can't thank you enough for your patience Bob.

Thank you again.

 

[paul1707]

Dates out of wack

Can you give me a specific name and date range to look at? I just took a look and it appears to work fine for me. The data has some missing info though so that could be causing the problem.

Hi Again Bob,

It would seem that the dates aren't working as i would like.

I have neclosed the code for the on click with the commented error trapping.
The report as shown in the gif is for the month of June but shows all the jobs for May.

Private Sub cmSIOK_Click()

'On Error GoTo Err_cmSIOK_Click

'If IsNull(Me.cboInstaller) = True Then
'MsgBox "Please select an Installer first", vbOKOnly
'Exit Sub
'End If
'If IsNull(Me.txtFrom) = True Then
'MsgBox "Please select a Start Date", vbOKOnly
'Else
'If IsNull(Me.txtTo) = True Then
'MsgBox "Please select an End Date", vbOKOnly
'Else
Me.Requery
DoCmd.OpenReport "rptAllJobs", acViewPreview, , "[InstLastName]='" & Me.cboInstaller & "' And [SchedInstallDate] between #" & Me.txtFrom & "# And #" & Me.txtTo & "#"

'DoCmd.Close acForm, Me.Name
'Exit Sub

'Exit_cmSIOK_Click:
'Exit Sub
'Err_cmSIOK_Click:
'MsgBox Err.Description
'Resume Exit_cmSIOK_Click
'End If

'End If
End Sub

It works the same with or without the error trapping.

Thanks Again

 

[boblarson]

I believe your problem is that you will need to pass the dates to the report in U.S. format.

 

[paul1707]

I believe your problem is that you will need to pass the dates to the report in U.S. format.

Will this do the job Bob?

Const conDateFormat = "\#mm\/dd\/yyyy\#"