Query reference a form control Access / SQL

[detrie]

Hello All,

I have an mdb front end hitting an SQL (2005) back end.

I have a form "frm_Reports" that lists reports and 2 controls "From" & "Thru"

The user selects a report, enters a "From" and "Thru" date and clicks "btnPrintReport"

The recordsouce on the Report is a SELECT query that references the frm_Reports From & Thru dates *****Is Null Or Between [forms]![frm_reports]![from] And [forms]![frm_reports]![thru]*****

I have +- 30 reports that are run this way

How can I pass these values using SQL as the back end?

TIA

Detrie

 

[pbaldy]

If you're using linked tables, you can use the query as is.

 

[detrie]

Hi Paul... Thansk for your reply.
I am using linked tables... However, I could only get it to work when I remove the *****Is Null Or Between [forms]![frm_reports]![from] And [forms]![frm_reports]![thru]*****
Do I need to reword it?
Detrie

 

[pbaldy]

What is the actual SQL? I assume the "*" aren't actually in it. A valid WHERE clause in an mdb might look like:

WHERE DateField Between Forms!FormName.ControlName And Forms!FormName.ControlName

 

[detrie]

Pul.
Here is the actual SQL..

SELECT tblProjectSegment.chkCreativeServiceNeeded, tblProjectSegment.strStatusID, tblProjectSegment.dtmScheduledDate
FROM tblCampaign INNER JOIN tblProjectSegment ON tblCampaign.lngProjectCodeID = tblProjectSegment.lngProjectCodeID
WHERE (((tblProjectSegment.chkCreativeServiceNeeded)=-1) AND ((tblProjectSegment.strStatusID)<>"Billed" And (tblProjectSegment.strStatusID)<>"Cancelled" And (tblProjectSegment.strStatusID)<>"Chessie" And (tblProjectSegment.strStatusID)<>"Complete" And (tblProjectSegment.strStatusID)<>"EOC Out" And (tblProjectSegment.strStatusID)<>"UTAD" And (tblProjectSegment.strStatusID)<>"Aborted") AND ((tblProjectSegment.dtmScheduledDate) Is Null Or (tblProjectSegment.dtmScheduledDate) Between [forms]![frm_reports]![from] And [forms]![frm_reports]![thru]) AND ((Left([strProjectCode],2))=[Forms]![frm_Reports]![cmbCountry]))
ORDER BY tblProjectSegment.dtmScheduledDate;


Thank you
Detrie

 

[pbaldy]

I should also have asked what's going wrong. Do you get an error, or unexpected results, or ? If it's unexpected results, I'd make sure the parentheses are doing what you really want, and I'd also wonder why tblCampaign is in the FROM clause, as it appears nowhere else.

 

[detrie]

Hi Paul
tblCampaign is a part of the live query... I removed the fields to save on space for this post...
I was not getting any records returned.. BUT I think I solved the issue...
I used upsizer to migrate into SQL Server. I had (BE.mdb) a yes/no field. Upsize made it bit length 1.... Part of my Query was looking for -1... When I remove the - in criteria... It runs like a champ.

I thank YOU for letting me know that the query should run as is. I was spinning my wheels trying to pass parameters from a form.

If I encounter another problem, I will be sure to post

Thank you for sticking with me and talking me off this ledge

Detrie

 

[pbaldy]

Glad you got it sorted out. I forgot SQL Server stored the bit value differently.

I often used stored procedures on SQL Server, which do require the passing of parameters, but as you see it will also work fine with linked tables.