Query problems.

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.

re: Query problems.

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?

re: Query problems.

-----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:
[color=blue]
> 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.
>
>
>[/color]

re: Query problems.

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:ReBnb.6352$RQ1.4496@newsread3.news.pas.earthl ink.net...[color=blue]
> -----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:
>[color=green]
> > 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[/color][/color]
box[color=blue][color=green]
> > 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[/color][/color]
fields[color=blue][color=green]
> > in T40 however instead of using the value of field it is simply using[/color][/color]
the[color=blue][color=green]
> > 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.
> >
> >
> >[/color]
>[/color]