Query based on form

[Guest]

Hello,

Here's a stupid question: I have an unbound form that has a combo box and
two textboxes to be used as dates. I built a query that will display
information based on a user filling in these fields. The query works fine
when I run it outside of the form. I get prompted to input the information
for these three fields and then the query displays the correct information.
However, if I run the query from the form after completing the three fields
there, nothing appears in the query. What am I doing wrong? Thank you in
advance!

Mark

 

[Guest]

Post your SQL statement.

 

[Guest]

Sorry, Thanks!

SELECT VoucherInfo.VoucherDate, VoucherInfo.LineofBusiness,
VoucherInfo.AccountantID, Detail.Payee, Detail.ContractNo, Detail.Amount,
Detail.VoidCk, Detail.ReissueCk, Detail.VoidReissueCkNo
FROM VoucherInfo INNER JOIN Detail ON VoucherInfo.VoucherNo = Detail.VoucherNo
WHERE (((VoucherInfo.VoucherDate) Between [Forms]![CustomReports]![FromDate]
And [Forms]![CustomReports]![ToDate]) AND
((VoucherInfo.LineofBusiness)=[Forms]![CustomReports]![CustRepLOB]));

 

[Roger Carlson]

You have to directly reference the controls on the form. So in your query,
instead of having something like this:

Select * from Table1 Where StartDate = [Start Date]

have

Select * from Table1 Where StartDate = Forms![FormName]![Start Date]

Where you replace [FormName] with the actual name of your form and [Start
Date] with the actual name of the start date text box.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Thanks, Roger, but I think that's already there...

WHERE (((VoucherInfo.VoucherDate) Between [Forms]![CustomReports]![FromDate]
And [Forms]![CustomReports]![ToDate]) AND
((VoucherInfo.LineofBusiness)=[Forms]![CustomReports]![CustRepLOB]));

Is the syntax wrong or something?

Thanks!
Mark

You have to directly reference the controls on the form. So in your query,
instead of having something like this:

Select * from Table1 Where StartDate = [Start Date]

have

Select * from Table1 Where StartDate = Forms![FormName]![Start Date]

Where you replace [FormName] with the actual name of your form and [Start
Date] with the actual name of the start date text box.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Hello,

Here's a stupid question: I have an unbound form that has a combo box and
two textboxes to be used as dates. I built a query that will display
information based on a user filling in these fields. The query works fine
when I run it outside of the form. I get prompted to input the information
for these three fields and then the query displays the correct information.
However, if I run the query from the form after completing the three fields
there, nothing appears in the query. What am I doing wrong? Thank you in
advance!

Mark

 

[Roger Carlson]

It looks like the syntax is correct. However, are you CERTAIN that the
names "FromDate" and "ToDate" and "CustRepLOB" are the actual names of the
textbox CONTROLS and not the names of the fields they are bound to? You
will get the empty recordset like you do if any of the control names are
incorrect or misspelled.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Thanks, Roger, but I think that's already there...

WHERE (((VoucherInfo.VoucherDate) Between [Forms]![CustomReports]![FromDate]
And [Forms]![CustomReports]![ToDate]) AND
((VoucherInfo.LineofBusiness)=[Forms]![CustomReports]![CustRepLOB]));

Is the syntax wrong or something?

Thanks!
Mark

You have to directly reference the controls on the form. So in your query,
instead of having something like this:

Select * from Table1 Where StartDate = [Start Date]

have

Select * from Table1 Where StartDate = Forms![FormName]![Start Date]

Where you replace [FormName] with the actual name of your form and [Start
Date] with the actual name of the start date text box.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Hello,

Here's a stupid question: I have an unbound form that has a combo box and
two textboxes to be used as dates. I built a query that will display
information based on a user filling in these fields. The query works fine
when I run it outside of the form. I get prompted to input the information
for these three fields and then the query displays the correct information.
However, if I run the query from the form after completing the three fields
there, nothing appears in the query. What am I doing wrong? Thank you in
advance!

Mark

 

[Guest]

One thing to do is test what the query is seeing like this --
SELECT VoucherInfo.VoucherDate, VoucherInfo.LineofBusiness,
VoucherInfo.AccountantID, Detail.Payee, Detail.ContractNo, Detail.Amount,
Detail.VoidCk, Detail.ReissueCk, Detail.VoidReissueCkNo,
[Forms]![CustomReports]![FromDate], [Forms]![CustomReports]![ToDate],
[Forms]![CustomReports]![CustRepLOB]
FROM VoucherInfo INNER JOIN Detail ON VoucherInfo.VoucherNo =
Detail.VoucherNo;

It looks like the syntax is correct. However, are you CERTAIN that the
names "FromDate" and "ToDate" and "CustRepLOB" are the actual names of the
textbox CONTROLS and not the names of the fields they are bound to? You
will get the empty recordset like you do if any of the control names are
incorrect or misspelled.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Thanks, Roger, but I think that's already there...

WHERE (((VoucherInfo.VoucherDate) Between [Forms]![CustomReports]![FromDate]
And [Forms]![CustomReports]![ToDate]) AND
((VoucherInfo.LineofBusiness)=[Forms]![CustomReports]![CustRepLOB]));

Is the syntax wrong or something?

Thanks!
Mark

You have to directly reference the controls on the form. So in your query,
instead of having something like this:

Select * from Table1 Where StartDate = [Start Date]

have

Select * from Table1 Where StartDate = Forms![FormName]![Start Date]

Where you replace [FormName] with the actual name of your form and [Start
Date] with the actual name of the start date text box.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Hello,

Here's a stupid question: I have an unbound form that has a combo box and
two textboxes to be used as dates. I built a query that will display
information based on a user filling in these fields. The query works fine
when I run it outside of the form. I get prompted to input the information
for these three fields and then the query displays the correct
information.
However, if I run the query from the form after completing the three
fields
there, nothing appears in the query. What am I doing wrong? Thank you in
advance!

Mark

 

[Guest]

Yes, those are the names of the controlls because those textboxes are not
bound to anything. Could the problem be that they are not bound to any
fields? I don't want them to be updated in a table or anything because once
they are used, that data does not need to be stored. Any ideas?

Thanks,
Mark

It looks like the syntax is correct. However, are you CERTAIN that the
names "FromDate" and "ToDate" and "CustRepLOB" are the actual names of the
textbox CONTROLS and not the names of the fields they are bound to? You
will get the empty recordset like you do if any of the control names are
incorrect or misspelled.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Thanks, Roger, but I think that's already there...

WHERE (((VoucherInfo.VoucherDate) Between [Forms]![CustomReports]![FromDate]
And [Forms]![CustomReports]![ToDate]) AND
((VoucherInfo.LineofBusiness)=[Forms]![CustomReports]![CustRepLOB]));

Is the syntax wrong or something?

Thanks!
Mark

You have to directly reference the controls on the form. So in your query,
instead of having something like this:

Select * from Table1 Where StartDate = [Start Date]

have

Select * from Table1 Where StartDate = Forms![FormName]![Start Date]

Where you replace [FormName] with the actual name of your form and [Start
Date] with the actual name of the start date text box.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Hello,

Here's a stupid question: I have an unbound form that has a combo box and
two textboxes to be used as dates. I built a query that will display
information based on a user filling in these fields. The query works fine
when I run it outside of the form. I get prompted to input the information
for these three fields and then the query displays the correct
information.
However, if I run the query from the form after completing the three
fields
there, nothing appears in the query. What am I doing wrong? Thank you in
advance!

Mark