reference in qry to a form's field not working

[Mark]

Anyone have an idea why the following is not working for me? I have an open form, which launches a process that runs queries. In one of the queries, I want to populate a field with the value from a text box on the form. (I have an end date field on form that user selects, want to list that same selected date in an 'as of date' column in query.) The query allows the syntax I'm using, but always returns an empty value. (And yes, the form is still open when query runs.)

Open form and field on it: [Forms]![iFrmRptSel]![txtEnd]

SQL of query: "SELECT "dB" AS Source, [Forms]![iFrmRptSel]![txtEnd] AS [As of], tblName.QUANTITY_ORDERED*tblName.LIST_PRICE_PER_UNIT*1.12 AS [Total Amount]
FROM tblName;"

Why does this not return the date value that is present in the form's field? Just returns empty cells, for the 'As of' column.

 

[SFAxess]

I assume that this query is the data source for a form,
so if you haven't already, try setting your form's
recordsource property in the Open event like this.

me.RecordSource="SELECT 'dB' AS Source, #" & [Forms]!
[iFrmRptSel]![txtEnd] & "# AS [As of],
tblName.QUANTITY_ORDERED*tblName.LIST_PRICE_PER_UNIT*1.12
AS [Total Amount] FROM tblName;"

Hope that works for you

-----Original Message-----
Anyone have an idea why the following is not working for

me? I have an open form, which launches a process that
runs queries. In one of the queries, I want to populate
a field with the value from a text box on the form. (I
have an end date field on form that user selects, want to
list that same selected date in an 'as of date' column in
query.) The query allows the syntax I'm using, but
always returns an empty value. (And yes, the form is
still open when query runs.)

Open form and field on it: [Forms]![iFrmRptSel]![txtEnd]

SQL of query: "SELECT "dB" AS Source, [Forms]!

[iFrmRptSel]![txtEnd] AS [As of],
tblName.QUANTITY_ORDERED*tblName.LIST_PRICE_PER_UNIT*1.12
AS [Total Amount]

FROM tblName;"

Why does this not return the date value that is present

in the form's field? Just returns empty cells, for
the 'As of' column.

 

[Mark]

it is not, in fact, the source for the form....

----- SFAxess wrote: -----

I assume that this query is the data source for a form,
so if you haven't already, try setting your form's
recordsource property in the Open event like this.

me.RecordSource="SELECT 'dB' AS Source, #" & [Forms]!
[iFrmRptSel]![txtEnd] & "# AS [As of],
tblName.QUANTITY_ORDERED*tblName.LIST_PRICE_PER_UNIT*1.12
AS [Total Amount] FROM tblName;"

Hope that works for you

-----Original Message-----
Anyone have an idea why the following is not working for

me? I have an open form, which launches a process that
runs queries. In one of the queries, I want to populate
a field with the value from a text box on the form. (I
have an end date field on form that user selects, want to
list that same selected date in an 'as of date' column in
query.) The query allows the syntax I'm using, but
always returns an empty value. (And yes, the form is
still open when query runs.)

Open form and field on it: [Forms]![iFrmRptSel]![txtEnd]
SQL of query: "SELECT "dB" AS Source, [Forms]!

[iFrmRptSel]![txtEnd] AS [As of],
tblName.QUANTITY_ORDERED*tblName.LIST_PRICE_PER_UNIT*1.12
AS [Total Amount]

FROM tblName;"

in the form's field? Just returns empty cells, for
the 'As of' column.

 

[SFAxess]

So then you are just opening the query and displaying it
to the user?
The SQL statement shown should work.
SELECT 'dB' AS Source, #" & [Forms]!
[iFrmRptSel]![txtEnd] & "# AS [As of],
tblName.QUANTITY_ORDERED*tblName.LIST_PRICE_PER_UNIT*1.12
AS [Total Amount] FROM tblName;"

I don't know if any data is entered in the text box that
holds the date, but if there is, you should make sure you
save the record before you run the query (i.e.
Docmd.RunCommand accmdSaveRecord)

-----Original Message-----
it is not, in fact, the source for the form....

----- SFAxess wrote: -----

I assume that this query is the data source for a form,
so if you haven't already, try setting your form's
recordsource property in the Open event like this.

me.RecordSource="SELECT 'dB' AS Source, #" & [Forms]!
[iFrmRptSel]![txtEnd] & "# AS [As of],
tblName.QUANTITY_ORDERED*tblName.LIST_PRICE_PER_UNIT*1.12
AS [Total Amount] FROM tblName;"

Hope that works for you

-----Original Message-----
Anyone have an idea why the following is not

working for
me? I have an open form, which launches a process that
runs queries. In one of the queries, I want to populate
a field with the value from a text box on the form. (I
have an end date field on form that user selects, want to
list that same selected date in an 'as of date' column in
query.) The query allows the syntax I'm using, but
always returns an empty value. (And yes, the form is
still open when query runs.)

Open form and field on it: [Forms]![iFrmRptSel]! [txtEnd]
SQL of query: "SELECT "dB" AS Source, [Forms]!

[iFrmRptSel]![txtEnd] AS [As of],
tblName.QUANTITY_ORDERED*tblName.LIST_PRICE_PER_UNIT*1.12
AS [Total Amount]

FROM tblName;"

present
in the form's field? Just returns empty cells, for
the 'As of' column.

.

.

 

[SteveS]

Anyone have an idea why the following is not working for me? I have an open form, which launches a process that runs queries. In one of the queries, I want to populate a field with the value from a text box on the form. (I have an end date field on form that user selects, want to list that same selected date in an 'as of date' column in query.) The query allows the syntax I'm using, but always returns an empty value. (And yes, the form is still open when query runs.)

Open form and field on it: [Forms]![iFrmRptSel]![txtEnd]

SQL of query: "SELECT "dB" AS Source, [Forms]![iFrmRptSel]![txtEnd] AS [As of], tblName.QUANTITY_ORDERED*tblName.LIST_PRICE_PER_UNIT*1.12 AS [Total Amount]
FROM tblName;"

Why does this not return the date value that is present in the form's field? Just returns empty cells, for the 'As of' column.

The only way I could duplicate your results (empty cells, for the 'As
of' column), was NOT to move to another control or NOT press 'enter'.

Try moving the focus to another control (or pressing enter) before
opening the query.

HTH