By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,790 Members | 1,398 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,790 IT Pros & Developers. It's quick & easy.

Query problems.

P: n/a
I am trying to create a query based on 1 of 3 date fields from a table
called "T40".
the first date is T40RbtDte, 2nd is T40RbtRecDte, 3rd is T40RbtDueDte.

I have created a form to alow the user to select what date the report is
based on. The report has a drop down box with 2 columns, and column 1
selected as the bound column.
The form is called "RptFrm Rebates Received by Vendor" and the drop down box
name is "DateRequested"
"(([T40]![T40RbtDte]))";"Rebate Earned Date";"((
[T40]![T40RbtRecDte] ))";"Rebate Received Date";"((
[T40]![T40RbtDueDte] ))";"Rebate Due Date"

I have a field in my query with a value of;
Date: [Forms]![RptFrm Rebates Received by Vendor]![DateRequested]
I am trying to get the query to print the Date from 1 of the above 3 fields
in T40 however instead of using the value of field it is simply using the
field name and the value is not printing.
Am I going about this properly or is there a easier way?
Any help would be greatly appreciated.

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Hmm... this looks ugly. From the looks of things, you're going to
have to modify the SQL underlying the report, because this isn't just
a filter. I'd do something like select the proper querydef that the
report should be based on, set it in the Open event of the report...
as for the non-applicable fields in your report... set their visible
property to false?
Nov 12 '05 #2

P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Queries do not recognize variables as column names. You will have to
use VBA to create the SQL statement w/ the appropriate reference.
E.g.:

dim strSQL as string

strSQL = "SELECT " & [Forms]![RptFrm Rebates Received by
Vendor]![DateRequested] & " FROM T40 "

This will pull the value of the ComboBox (the column name) and place
it in the SQL string. Then you can put that string into a query -

dim db as dao.database
set db = currentdb
dim qd as dao.querydef
set qd = db.querydefs("GetDates")
qd.sql = strSQL
qd.close

Then run the query or use it in a report.

Or, use the SQL string to open a recordset using VBA -

dim rs as dao.recordset
set rs = db.openrecordset(strSQL)
.... etc. ...

HTH,

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP57gKYechKqOuFEgEQJwGgCeJuVvVCPG2WXQHjzUB5P05a 7egEIAoPzv
J0zjaXzLY7TGqsoMtk+huliN
=b6r/
-----END PGP SIGNATURE-----

Reginald wrote:
I am trying to create a query based on 1 of 3 date fields from a table
called "T40".
the first date is T40RbtDte, 2nd is T40RbtRecDte, 3rd is T40RbtDueDte.

I have created a form to alow the user to select what date the report is
based on. The report has a drop down box with 2 columns, and column 1
selected as the bound column.
The form is called "RptFrm Rebates Received by Vendor" and the drop down box
name is "DateRequested"
"(([T40]![T40RbtDte]))";"Rebate Earned Date";"((
[T40]![T40RbtRecDte] ))";"Rebate Received Date";"((
[T40]![T40RbtDueDte] ))";"Rebate Due Date"

I have a field in my query with a value of;
Date: [Forms]![RptFrm Rebates Received by Vendor]![DateRequested]
I am trying to get the query to print the Date from 1 of the above 3 fields
in T40 however instead of using the value of field it is simply using the
field name and the value is not printing.
Am I going about this properly or is there a easier way?
Any help would be greatly appreciated.


Nov 12 '05 #3

P: n/a
Thanks for your help I'm new to SQL so I don't have the knowledge at this
point to know what your response means but I will research it to get it to
work.
Thanks Again,
Reginald,
London, Ontario
"MGFoster" <me@privacy.com> wrote in message
news:Re*****************@newsread3.news.pas.earthl ink.net...
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Queries do not recognize variables as column names. You will have to
use VBA to create the SQL statement w/ the appropriate reference.
E.g.:

dim strSQL as string

strSQL = "SELECT " & [Forms]![RptFrm Rebates Received by
Vendor]![DateRequested] & " FROM T40 "

This will pull the value of the ComboBox (the column name) and place
it in the SQL string. Then you can put that string into a query -

dim db as dao.database
set db = currentdb
dim qd as dao.querydef
set qd = db.querydefs("GetDates")
qd.sql = strSQL
qd.close

Then run the query or use it in a report.

Or, use the SQL string to open a recordset using VBA -

dim rs as dao.recordset
set rs = db.openrecordset(strSQL)
... etc. ...

HTH,

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP57gKYechKqOuFEgEQJwGgCeJuVvVCPG2WXQHjzUB5P05a 7egEIAoPzv
J0zjaXzLY7TGqsoMtk+huliN
=b6r/
-----END PGP SIGNATURE-----

Reginald wrote:
I am trying to create a query based on 1 of 3 date fields from a table
called "T40".
the first date is T40RbtDte, 2nd is T40RbtRecDte, 3rd is T40RbtDueDte.

I have created a form to alow the user to select what date the report is
based on. The report has a drop down box with 2 columns, and column 1
selected as the bound column.
The form is called "RptFrm Rebates Received by Vendor" and the drop down box name is "DateRequested"
"(([T40]![T40RbtDte]))";"Rebate Earned Date";"((
[T40]![T40RbtRecDte] ))";"Rebate Received Date";"((
[T40]![T40RbtDueDte] ))";"Rebate Due Date"

I have a field in my query with a value of;
Date: [Forms]![RptFrm Rebates Received by Vendor]![DateRequested]
I am trying to get the query to print the Date from 1 of the above 3 fields in T40 however instead of using the value of field it is simply using the field name and the value is not printing.
Am I going about this properly or is there a easier way?
Any help would be greatly appreciated.

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.