DoCmd.OpenReport WHERE-syntacs problem

[Saisaku]

Hello

I just can't get this piece working! I'm pulling out my hairs right now ...
I hope one of you can point me to the problem ...

I just paste the part where I have a problem with:

Dim dtStart As Date
Dim dtEind As Date
' ...

If Me.cmbKwartaal.Value = "Q1" Then
    dtStart = "01/01/" & Format(Date, "yyyy")
    dtEind = "31/03/" & Format(Date, "yyyy")
ElseIf Me.cmbKwartaal = "Q2" Then
    dtStart = "01/04/" & Format(Date, "yyyy")
    dtEind = "30/06/" & Format(Date, "yyyy")
ElseIf Me.cmbKwartaal = "Q3" Then
    dtStart = "01/07/" & Format(Date, "yyyy")
    dtEind = "30/09/" & Format(Date, "yyyy")
ElseIf Me.cmbKwartaal = "Q4" Then
    dtStart = "01/10/" & ((Format(Date, "yyyy")) - 1)
    dtEind = "31/12/" & ((Format(Date, "yyyy")) - 1)
Else
    Call MsgBox("Error", vbOKOnly)
End If
'...

'Here I have my problem:
DoCmd.OpenReport "rptPayment", acViewPreview, , _
    "(([logPersoneel.prsID]=" & RST!prsID _
    & ") AND ([logInterventies.intDatum]>=#" & dtStart _
    & "#) AND ([logInterventies.intDatum]<=#" & dtEind & "#));", acHidden

If I put this SQL in a querry, it works perfect ...
If I cut the SQL before the first "AND", it works perfect ...

Many thanks
Kurt

 

[pbaldy]

Don't try to cut in the middle of text. Try

DoCmd.OpenReport "rptPayment", acViewPreview, , _
    "(([logPersoneel.prsID]=" & RST!prsID _
  & ") AND ([logInterventies.intDatum]>=#" & dtStart _
  & "#) AND ([logInterventies.intDatum]<=#" & dtEind & "#));", acHidden

 

[Saisaku]

Thanx, I will try to remeber that, and change the first post.

But as I said in the original post, I do not use underscores in my code, so the problem of the syntax-error isn't solved yet

 

[pbaldy]

Well, I copied that from yours, and the underscores are line-continuation characters. If you don't want them it's all on one line:

DoCmd.OpenReport "rptPayment", acViewPreview, ,  "(([logPersoneel.prsID]=" & RST!prsID  & ") AND ([logInterventies.intDatum]>=#" & dtStart & "#) AND ([logInterventies.intDatum]<=#" & dtEind & "#));", acHidden

 

[Saisaku]

Thanx, but the syntax-error is still there. There is a problem in my "WHERE"-part and I can't find it ...

 

[pbaldy]

Try adding

Dim strSQL As String

strSQL = "[logPersoneel.prsID]=" & RST!prsID  & " AND [logInterventies.intDatum]>=#" & dtStart & "# AND [logInterventies.intDatum]<=#" & dtEind & "#"

Debug.Print strSQL
DoCmd.OpenReport "rptPayment", acViewPreview, ,  strSQL, acHidden

so we can see the finished string in the Immediate window:

http://www.baldyweb.com/ImmediateWindow.htm

Do your field names really have periods in them? If that's actually Table.Field take the brackets off.

 

[Saisaku]

The Immediate Window gave me this:

(([logPersoneel.prsID]=2) AND ([logInterventies.intDatum]>=#01/10/2012#) AND ([logInterventies.intDatum]<=#31/12/2012#));

Do your field names really have periods in them? If that's actually Table.Field take the brackets off.

No, I don't use periodes in my fieldnames. This is just the way I learned this.
I did what you asked, removed all the squared brackets, but with no results. Changed them to round brackets, no results ...

many thanks

 

[pbaldy]

This:

[logInterventies.intDatum]

makes Access think the whole bit is a field name, which it won't be able to find. You want one of these (the brackets are only required if there are spaces or symbols in the field name, or it's a reserved word like "Date"):

[logInterventies].[intDatum]
logInterventies.intDatum

Looking at the result, you're also going to run into this:

http://allenbrowne.com/ser-36.html#Format
http://access.mvps.org/access/datetime/date0005.htm

If it still isn't working, can you post the db here?

 

[Saisaku]

I tried to upload the database in ZIP-file, but it didn't succeed.

I will give the full code, maybe you see the problem ... :

Option Compare Database
Private Sub cmdSend_Click()
    Dim DB As DAO.Database
    Dim RST As DAO.Recordset
    Dim OUT As Outlook.Application
    Dim REC As Outlook.Recipient
    Dim MSG As Outlook.MailItem
    Dim ATT As Outlook.Attachment
    Dim strSQL As String
    Dim strSQL2 As String
    Dim strObjID As String
    Dim strMail As String
    Dim strPath As String
    Dim dtStart As Date
    Dim dtEind As Date
    
    'Bepalen kwartaal
    '----------------
    
    If Me.cmbKwartaal.Value = "Q1" Then
        dtStart = "01/01/" & Format(Date, "yyyy")
        dtEind = "31/03/" & Format(Date, "yyyy")
    ElseIf Me.cmbKwartaal = "Q2" Then
        dtStart = "01/04/" & Format(Date, "yyyy")
        dtEind = "30/06/" & Format(Date, "yyyy")
    ElseIf Me.cmbKwartaal = "Q3" Then
        dtStart = "01/07/" & Format(Date, "yyyy")
        dtEind = "30/09/" & Format(Date, "yyyy")
    ElseIf Me.cmbKwartaal = "Q4" Then
        dtStart = "01/10/" & ((Format(Date, "yyyy")) - 1)
        dtEind = "31/12/" & ((Format(Date, "yyyy")) - 1)
    Else
        Call MsgBox("Error", vbOKOnly)
    End If
    
    Call MsgBox("Start: " & dtStart & _
        vbCrLf & "Eind: " & dtEind, vbOKOnly)
    
    
    
    
    Set DB = CurrentDb
    
    
    strSQL = "SELECT logPersoneel.prsID, logPersoneel.prsName, logPersoneel.prsForename, logPersoneel.prsJPHmail FROM logPersoneel WHERE (((logPersoneel.prsJPHmail) Is Not Null));"
    Set RST = DB.OpenRecordset(strSQL)
    RST.MoveFirst
    
    Do Until RST.EOF
    
    strObjID = RST!prsID
    strMail = RST!prsJPHmail
    
    
    'Aanmaken en Opslaan van het rapport
    '-----------------------------------
    strPath = CurrentProject.Path & "\Aanwezigheid\" & RST!prsName & " " & RST!prsForename & ".pdf"
    strSQL2 = "logPersoneel.prsID=" & RST!prsID & " AND logInterventies.intDatum>=#" & Format(dtStart, "mm\/dd\/yyyy") & "# AND logInterventies.intDatum<=#" & Format(dtEind, "mm\/dd\/yyyy") & "#;"
    
    Call MsgBox(strSQL2, vbOKCancel)
    
    Debug.Print strSQL2
    DoCmd.OpenReport "rptPayment", acViewPreview, , strSQL2, acHidden
    
    
    DoCmd.OutputTo acOutputReport, "rptPayment", acFormatPDF, strPath, False
    DoCmd.Close acReport, "rptPayment", acSaveNo
    
    
    
    
    
    
    'Afwerken
    '--------
    RST.MoveNext
    Loop
End Sub

I checked every table and field name to be sure, everything seems good ...

 

[pbaldy]

See if this helps:

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

 

[Saisaku]

Yes, did that, but I see no the size limit of 2MB ... If I try to compress and zip the databse, I have a 8MB ...

 

[Cronk]

I haven't read all the thread closely but I see the date criteria format is dd/mm/yyyy

It should be mm/dd/yyyy

 

[pbaldy]

I haven't read all the thread closely but I see the date criteria format is dd/mm/yyyy

It should be mm/dd/yyyy

You should have read more closely.

 

[Saisaku]

Found the problem How one symbol can give you haedaches for days ...

strSQL2 = "logPersoneel.prsID=" & RST!prsID & " AND logInterventies.intDatum>=#" _
& Format(dtStart, "mm\/dd\/yyyy") & "# AND logInterventies.intDatum<=#" _
& Format(dtEind, "mm\/dd\/yyyy") & "#[COLOR="Red"][B];[/B][/COLOR]"

Removed the ";" and it worked ...

Thank you for your help pbaldy .

 

[pbaldy]

Happy to help!

 

[namliam]

You should have read more closely.

Actually cronk is right....

    Dim dtStart As Date
    Dim dtEind As Date
    
    'Bepalen kwartaal
    '----------------
    
    If Me.cmbKwartaal.Value = "Q1" Then
        dtStart = "01/01/" & Format(Date, "yyyy")
        dtEind = "31/03/" & Format(Date, "yyyy")

not only are these dates in DD/MM/YYYY format, also they rely on implicit conversion where they do not need to...

A better way would be to do...

        dtStart = Dateserial(Year(date), 1,1)
        dtEind = Dateserial(year(date), 3,31)

And just for readability sake... the sql can be a lot cleaner...

strSQL2 = "logPersoneel.prsID=" & RST!prsID & " AND logInterventies.intDatum>=#" _
& Format(dtStart, "mm\/dd\/yyyy") & "# AND logInterventies.intDatum<=#" _
& Format(dtEind, "mm\/dd\/yyyy") & "#;"

Is just an unreadable mess IMNSHO

strSQL2 = "     logPersoneel.prsID=" & RST!prsID & _
          " AND logInterventies.intDatum>=#" & Format(dtStart, "mm\/dd\/yyyy") & "#" & _
          " AND logInterventies.intDatum<=#" & Format(dtEind, "mm\/dd\/yyyy") & "#"

On top of which, have you considered using the datepart function to derive the quarter instead of using the <= >= construct? Unless offcourse your intDatum is indexed in which case the <= >= is obviously better.

 

[Cronk]

Actually Paul knows I am right. He was just gently chiding me for not reading the references he provided that made the same point earlier.

 

[namliam]

Guess I too did read the thread too fast :/

 

[pbaldy]

Actually Paul knows I am right. He was just gently chiding me for not reading the references he provided that made the same point earlier.

No, I didn't think you were right. OP had handled the date format in building the SQL.