dynamic query

M

MJ

Hi,

I'm trying to create a search page, where the user can enter certain
fields and it will look them up in the database and spit out the
results in a report.

Is there a way to do this in the code? Ie. I want to dynamically
build my sql statement. I'm just not sure how to pass it to the
report so that the report uses the sql query I put together. Any
ideas?

Thanks,
MJ
 
M

Martin J

In report open event use code builder it will look like this...

Private Sub Report_Open(cancel As Integer)

Dim sqlstr As String, a As String
a = dw1(Forms![zform1]![from]) 'field name from a form
With Forms![zform1] ' so i dont need to keep rewriting it
sqlstr = "SELECT table.* " & _
"FROM table LEFT JOIN shift ON table.[preferred shift] = table1.[Shift #] "
& _
"WHERE (((table.[name]) Between '" & ![ln from] & "' AND '" & ![ln to] & "')
AND ((table1.[Shift Name]) Between '" & ![sfrom] & "' AND '" & ![sto] & "')
AND ((table." & a & ")=True));" 'my dynamic sql

Me.RecordSource = sqlstr 'telling the report my record source

....

HTH
Martin J
 
P

PC Datasheet

Look at QueryDef in the Help file. Basically you create a query with a name,
dynamically create the SQL for that query and then use the query as the
recordsource for the report.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top