sql query help

[Br1an_g]

hey there,
ive been working on some enhancements of our works access based customer database.
background: a customer is added to the database, the date is filled as the take on date(cdate), when added the customer takes one or more modules (choice of three), the date the module is taken is also auto filled (Module?AddedDeletedDate). what is required is a report of the additional modules in a given date period.
ive managed to get it to show the added modules between the specified dates but the problem is this-if a customer is registered and takes module one, then two weeks later takes module 2, then 6 months later takes module 3. if a report is generated for the date range covering the addition of module three then the report also shows the addition of module two when it is only required to show the most recent module addition.

dont know if im making sense but here is the what i have done so far:

Private Sub cmdModules_Click()
'Perpares a database sheet detailing all modules added or
'removed in the given date period

Dim date1 As String
Dim date2 As String
Dim continue As Boolean
Dim retvalue As Variant

On Error GoTo Err_Module_Sheet_Click

Call getDates(date1, date2, continue)

If continue = True Then
    Call DelTables("DBSHEET")
    DoCmd.RunSQL ("SELECT SortCode,Q6Code,Area,CustomerName,Module1") & _
                (", Module2, Module3,Module1AddedDeletedDate,Module2AddedDeletedDate") & _
                (",Module3AddedDeletedDate,Module1Added,Module2Added,Module3Added,cdate ") & _
                (" INTO DBSHEET") & _
                (" FROM Customer ") & _
                ("WHERE Module1AddedDeletedDate > cdate") & _
("  AND( Module1AddedDeletedDate >= #" & date1 & "# AND Module1AddedDeletedDate <= #" & date2 & "#)") & _
(" OR Module2AddedDeletedDate > cdate") & _
("  AND( Module2AddedDeletedDate >= #" & date1 & "# AND Module2AddedDeletedDate <= #" & date2 & "#)") & _
(" OR Module3AddedDeletedDate > cdate") & _
("  AND( Module3AddedDeletedDate >= #" & date1 & "# AND Module3AddedDeletedDate <= #" & date2 & "#)") & _
(" AND Module1Added = True OR Module2Added = True OR Module3Added = True";)

retvalue = MsgBox("Do you wish to print this report ?", vbYesNo, "Printing")
    If retvalue = vbYes Then
        DoCmd.OpenReport "AdditionalModulesReport", acViewPreview
        DoCmd.OpenReport "AdditionalModulesReport", acViewPreview
    End If
  
  End If
Exit_Module_Sheet_Click:
    Exit Sub

Err_Module_Sheet_Click:
    MsgBox Err.Description
    Resume Exit_Module_Sheet_Click
End Sub

Any and all help is appreciated

Brian

[jdc2000]

Bump.

[jim mcnamara]

I don't know why there are parens around your sql statement
on every line but:
And x or y or z

is your trouble

you want

and (x or y or z)

AND (Module1Added = True OR Module2Added = True OR Module3Added = True)"; )

[Br1an_g]

thanks for the feedback,

the section of code you refer to was quickly added as the copy i had at home was older than the one in work but i knew i had added a section to ensure that it was modules added only. (there is a module1added or module1deleted option)

that is not where my (main) problem lies.

what i need is for my sql statement to take only the newest module addition and add that to the report, as described im my first post-so that when the report is generated it will show the customers details and then only the modules added between date1 and date2.

im not even sure if its possible to limit what details are taken onto a report from the query.

is there another option open to me other than sql?

Brian

ps thanks for the bump jdc2000