perplexed on query from form

J

javablood

I am trying to run two queries from a form (a Select and a Crosstab). Both
have a criteria ([Forms]![frm_NWBA_1]![datbegin]) in the date field that is
input from the user. For some unknown reason (as least to me) when I try to
run either both queries using OpenQuery in an event procedure or just the
crosstab query I get an error saying '[Forms]![frm_NWBA_1]![datbegin]' is
not recognized as a valid field name or expression. But this only happens
with the crosstab query, and if I hard code a date the crosstab query runs
just fine.

Alternatively, I have tried to code the SQL into a subform and use the
OpenQuery in an event procedure but then I get a Type Mismatch error from the
following:

Dim datqry As String

datqry = "TRANSFORM First(Hardage.RESULT) AS FirstOfRESULT " & _
"SELECT Hardage.PARAMETER, Hardage_PAR.F1_GRPORDR, " & _
"Hardage_Site_Identification.ID_Group, Hardage.STATION_ID " & _
"FROM Hardage_PAR INNER JOIN (Hardage_Site_Identification " & _
"INNER JOIN Hardage ON Hardage_Site_Identification.STATION_ID = " & _
"Hardage.STATION_ID) ON Hardage_PAR.PARAMETER = Hardage.PARAMETER " & _
"WHERE (((Hardage_Site_Identification.ID_Group) Like " * NWBA * ") " & _
"And ((Hardage.SAMPLE_DATE) >
Forms.frm_NWBA_1.datbegin.Form.cmddatreset) And ((Hardage.QC) = ""O"")) " & _
"GROUP BY Hardage.PARAMETER, Hardage_PAR.F1_GRPORDR,
Hardage_Site_Identification.ID_Group, " & _
"Hardage.STATION_ID ORDER BY Hardage_PAR.F1_GRPORDR PIVOT
Hardage.SAMPLE_DATE;"

DoCmd.OpenQuery "datqry", acViewDesign

Admittedly, I may have the relationship of Parent form and Subform mixed up
in Forms.frm_NWBA_1.datbegin.Form.cmddatreset (frm_NWBA_1 is Parent Form;
datbegin is control in Parent form, and cmddatreset is control in subform)
but I have even tried to just run the query from the subform using
[Forms]![frm_NWBA_2]![datx] in the query criteria for the date where datx is
input in the subform (just like 'datbegin' in the parent form) but I still
get the not recognized as a valid field name or expression error.

I am a 'learn as I go' type and the discussion group helps me to learn when
I have exhausted other resources but I have not been able to find my answer
through fourms, discussions, etc. Does anyone have an answer?

TIA!
 
K

KARL DEWEY

Open crosstab query in SQL view and add this before TRANSFORM line --
PARAMETERS [Forms]![frm_NWBA_1]![datbegin] DateTime;
 
J

javablood

Karl,

I tried that separately in both the query (called from the parent form) and
in the datqry (called from event procedure in the subform) and neither
worked. The query returned a blank page with just the headers and the datqyr
still gave the type mismatch error.
--
javablood


KARL DEWEY said:
Open crosstab query in SQL view and add this before TRANSFORM line --
PARAMETERS [Forms]![frm_NWBA_1]![datbegin] DateTime;

javablood said:
I am trying to run two queries from a form (a Select and a Crosstab). Both
have a criteria ([Forms]![frm_NWBA_1]![datbegin]) in the date field that is
input from the user. For some unknown reason (as least to me) when I try to
run either both queries using OpenQuery in an event procedure or just the
crosstab query I get an error saying '[Forms]![frm_NWBA_1]![datbegin]' is
not recognized as a valid field name or expression. But this only happens
with the crosstab query, and if I hard code a date the crosstab query runs
just fine.

Alternatively, I have tried to code the SQL into a subform and use the
OpenQuery in an event procedure but then I get a Type Mismatch error from the
following:

Dim datqry As String

datqry = "TRANSFORM First(Hardage.RESULT) AS FirstOfRESULT " & _
"SELECT Hardage.PARAMETER, Hardage_PAR.F1_GRPORDR, " & _
"Hardage_Site_Identification.ID_Group, Hardage.STATION_ID " & _
"FROM Hardage_PAR INNER JOIN (Hardage_Site_Identification " & _
"INNER JOIN Hardage ON Hardage_Site_Identification.STATION_ID = " & _
"Hardage.STATION_ID) ON Hardage_PAR.PARAMETER = Hardage.PARAMETER " & _
"WHERE (((Hardage_Site_Identification.ID_Group) Like " * NWBA * ") " & _
"And ((Hardage.SAMPLE_DATE) >
Forms.frm_NWBA_1.datbegin.Form.cmddatreset) And ((Hardage.QC) = ""O"")) " & _
"GROUP BY Hardage.PARAMETER, Hardage_PAR.F1_GRPORDR,
Hardage_Site_Identification.ID_Group, " & _
"Hardage.STATION_ID ORDER BY Hardage_PAR.F1_GRPORDR PIVOT
Hardage.SAMPLE_DATE;"

DoCmd.OpenQuery "datqry", acViewDesign

Admittedly, I may have the relationship of Parent form and Subform mixed up
in Forms.frm_NWBA_1.datbegin.Form.cmddatreset (frm_NWBA_1 is Parent Form;
datbegin is control in Parent form, and cmddatreset is control in subform)
but I have even tried to just run the query from the subform using
[Forms]![frm_NWBA_2]![datx] in the query criteria for the date where datx is
input in the subform (just like 'datbegin' in the parent form) but I still
get the not recognized as a valid field name or expression error.

I am a 'learn as I go' type and the discussion group helps me to learn when
I have exhausted other resources but I have not been able to find my answer
through fourms, discussions, etc. Does anyone have an answer?

TIA!
Click to expand...
Click to expand...
 
K

KARL DEWEY

Have you tried to put the SQL in a query instead of the form?

javablood said:
Karl,

I tried that separately in both the query (called from the parent form) and
in the datqry (called from event procedure in the subform) and neither
worked. The query returned a blank page with just the headers and the datqyr
still gave the type mismatch error.
--
javablood


KARL DEWEY said:
Open crosstab query in SQL view and add this before TRANSFORM line --
PARAMETERS [Forms]![frm_NWBA_1]![datbegin] DateTime;

javablood said:
I am trying to run two queries from a form (a Select and a Crosstab). Both
have a criteria ([Forms]![frm_NWBA_1]![datbegin]) in the date field that is
input from the user. For some unknown reason (as least to me) when I try to
run either both queries using OpenQuery in an event procedure or just the
crosstab query I get an error saying '[Forms]![frm_NWBA_1]![datbegin]' is
not recognized as a valid field name or expression. But this only happens
with the crosstab query, and if I hard code a date the crosstab query runs
just fine.

Alternatively, I have tried to code the SQL into a subform and use the
OpenQuery in an event procedure but then I get a Type Mismatch error from the
following:

Dim datqry As String

datqry = "TRANSFORM First(Hardage.RESULT) AS FirstOfRESULT " & _
"SELECT Hardage.PARAMETER, Hardage_PAR.F1_GRPORDR, " & _
"Hardage_Site_Identification.ID_Group, Hardage.STATION_ID " & _
"FROM Hardage_PAR INNER JOIN (Hardage_Site_Identification " & _
"INNER JOIN Hardage ON Hardage_Site_Identification.STATION_ID = " & _
"Hardage.STATION_ID) ON Hardage_PAR.PARAMETER = Hardage.PARAMETER " & _
"WHERE (((Hardage_Site_Identification.ID_Group) Like " * NWBA * ") " & _
"And ((Hardage.SAMPLE_DATE) >
Forms.frm_NWBA_1.datbegin.Form.cmddatreset) And ((Hardage.QC) = ""O"")) " & _
"GROUP BY Hardage.PARAMETER, Hardage_PAR.F1_GRPORDR,
Hardage_Site_Identification.ID_Group, " & _
"Hardage.STATION_ID ORDER BY Hardage_PAR.F1_GRPORDR PIVOT
Hardage.SAMPLE_DATE;"

DoCmd.OpenQuery "datqry", acViewDesign

Admittedly, I may have the relationship of Parent form and Subform mixed up
in Forms.frm_NWBA_1.datbegin.Form.cmddatreset (frm_NWBA_1 is Parent Form;
datbegin is control in Parent form, and cmddatreset is control in subform)
but I have even tried to just run the query from the subform using
[Forms]![frm_NWBA_2]![datx] in the query criteria for the date where datx is
input in the subform (just like 'datbegin' in the parent form) but I still
get the not recognized as a valid field name or expression error.

I am a 'learn as I go' type and the discussion group helps me to learn when
I have exhausted other resources but I have not been able to find my answer
through fourms, discussions, etc. Does anyone have an answer?

TIA!
Click to expand...
Click to expand...
Click to expand...
 
J

javablood

Karl,

Yes. I tried your recommendation in the code in the Event procedure in the
subform and it did not work - Type Mistmatch.

Then, separately, I tried your recommendation in a query (ctab_NWBA) that is
called from the parent form and it gave me the blank form with just headers.
At least with this one I got something this time and not the "not recognized"
error.
--
javablood


KARL DEWEY said:
Have you tried to put the SQL in a query instead of the form?

javablood said:
Karl,

I tried that separately in both the query (called from the parent form) and
in the datqry (called from event procedure in the subform) and neither
worked. The query returned a blank page with just the headers and the datqyr
still gave the type mismatch error.
--
javablood


KARL DEWEY said:
Open crosstab query in SQL view and add this before TRANSFORM line --
PARAMETERS [Forms]![frm_NWBA_1]![datbegin] DateTime;

:

I am trying to run two queries from a form (a Select and a Crosstab). Both
have a criteria ([Forms]![frm_NWBA_1]![datbegin]) in the date field that is
input from the user. For some unknown reason (as least to me) when I try to
run either both queries using OpenQuery in an event procedure or just the
crosstab query I get an error saying '[Forms]![frm_NWBA_1]![datbegin]' is
not recognized as a valid field name or expression. But this only happens
with the crosstab query, and if I hard code a date the crosstab query runs
just fine.

Alternatively, I have tried to code the SQL into a subform and use the
OpenQuery in an event procedure but then I get a Type Mismatch error from the
following:

Dim datqry As String

datqry = "TRANSFORM First(Hardage.RESULT) AS FirstOfRESULT " & _
"SELECT Hardage.PARAMETER, Hardage_PAR.F1_GRPORDR, " & _
"Hardage_Site_Identification.ID_Group, Hardage.STATION_ID " & _
"FROM Hardage_PAR INNER JOIN (Hardage_Site_Identification " & _
"INNER JOIN Hardage ON Hardage_Site_Identification.STATION_ID = " & _
"Hardage.STATION_ID) ON Hardage_PAR.PARAMETER = Hardage.PARAMETER " & _
"WHERE (((Hardage_Site_Identification.ID_Group) Like " * NWBA * ") " & _
"And ((Hardage.SAMPLE_DATE) >
Forms.frm_NWBA_1.datbegin.Form.cmddatreset) And ((Hardage.QC) = ""O"")) " & _
"GROUP BY Hardage.PARAMETER, Hardage_PAR.F1_GRPORDR,
Hardage_Site_Identification.ID_Group, " & _
"Hardage.STATION_ID ORDER BY Hardage_PAR.F1_GRPORDR PIVOT
Hardage.SAMPLE_DATE;"

DoCmd.OpenQuery "datqry", acViewDesign

Admittedly, I may have the relationship of Parent form and Subform mixed up
in Forms.frm_NWBA_1.datbegin.Form.cmddatreset (frm_NWBA_1 is Parent Form;
datbegin is control in Parent form, and cmddatreset is control in subform)
but I have even tried to just run the query from the subform using
[Forms]![frm_NWBA_2]![datx] in the query criteria for the date where datx is
input in the subform (just like 'datbegin' in the parent form) but I still
get the not recognized as a valid field name or expression error.

I am a 'learn as I go' type and the discussion group helps me to learn when
I have exhausted other resources but I have not been able to find my answer
through fourms, discussions, etc. Does anyone have an answer?

TIA!
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

Did you run just the query, not from a form?

But you know that you need the form open and data entered into the datbegin
text box.

javablood said:
Karl,

Yes. I tried your recommendation in the code in the Event procedure in the
subform and it did not work - Type Mistmatch.

Then, separately, I tried your recommendation in a query (ctab_NWBA) that is
called from the parent form and it gave me the blank form with just headers.
At least with this one I got something this time and not the "not recognized"
error.
--
javablood


KARL DEWEY said:
Have you tried to put the SQL in a query instead of the form?

javablood said:
Karl,

I tried that separately in both the query (called from the parent form) and
in the datqry (called from event procedure in the subform) and neither
worked. The query returned a blank page with just the headers and the datqyr
still gave the type mismatch error.
--
javablood


:

Open crosstab query in SQL view and add this before TRANSFORM line --
PARAMETERS [Forms]![frm_NWBA_1]![datbegin] DateTime;

:

I am trying to run two queries from a form (a Select and a Crosstab). Both
have a criteria ([Forms]![frm_NWBA_1]![datbegin]) in the date field that is
input from the user. For some unknown reason (as least to me) when I try to
run either both queries using OpenQuery in an event procedure or just the
crosstab query I get an error saying '[Forms]![frm_NWBA_1]![datbegin]' is
not recognized as a valid field name or expression. But this only happens
with the crosstab query, and if I hard code a date the crosstab query runs
just fine.

Alternatively, I have tried to code the SQL into a subform and use the
OpenQuery in an event procedure but then I get a Type Mismatch error from the
following:

Dim datqry As String

datqry = "TRANSFORM First(Hardage.RESULT) AS FirstOfRESULT " & _
"SELECT Hardage.PARAMETER, Hardage_PAR.F1_GRPORDR, " & _
"Hardage_Site_Identification.ID_Group, Hardage.STATION_ID " & _
"FROM Hardage_PAR INNER JOIN (Hardage_Site_Identification " & _
"INNER JOIN Hardage ON Hardage_Site_Identification.STATION_ID = " & _
"Hardage.STATION_ID) ON Hardage_PAR.PARAMETER = Hardage.PARAMETER " & _
"WHERE (((Hardage_Site_Identification.ID_Group) Like " * NWBA * ") " & _
"And ((Hardage.SAMPLE_DATE) >
Forms.frm_NWBA_1.datbegin.Form.cmddatreset) And ((Hardage.QC) = ""O"")) " & _
"GROUP BY Hardage.PARAMETER, Hardage_PAR.F1_GRPORDR,
Hardage_Site_Identification.ID_Group, " & _
"Hardage.STATION_ID ORDER BY Hardage_PAR.F1_GRPORDR PIVOT
Hardage.SAMPLE_DATE;"

DoCmd.OpenQuery "datqry", acViewDesign

Admittedly, I may have the relationship of Parent form and Subform mixed up
in Forms.frm_NWBA_1.datbegin.Form.cmddatreset (frm_NWBA_1 is Parent Form;
datbegin is control in Parent form, and cmddatreset is control in subform)
but I have even tried to just run the query from the subform using
[Forms]![frm_NWBA_2]![datx] in the query criteria for the date where datx is
input in the subform (just like 'datbegin' in the parent form) but I still
get the not recognized as a valid field name or expression error.

I am a 'learn as I go' type and the discussion group helps me to learn when
I have exhausted other resources but I have not been able to find my answer
through fourms, discussions, etc. Does anyone have an answer?

TIA!
Click to expand...
Click to expand...
Click to expand...
 
J

javablood

Yes. I opened the form, entered the date, then ran the query ouside of the
form and still came up blank!
--
javablood


KARL DEWEY said:
Did you run just the query, not from a form?

But you know that you need the form open and data entered into the datbegin
text box.

javablood said:
Karl,

Yes. I tried your recommendation in the code in the Event procedure in the
subform and it did not work - Type Mistmatch.

Then, separately, I tried your recommendation in a query (ctab_NWBA) that is
called from the parent form and it gave me the blank form with just headers.
At least with this one I got something this time and not the "not recognized"
error.
--
javablood


KARL DEWEY said:
Have you tried to put the SQL in a query instead of the form?

:

Karl,

I tried that separately in both the query (called from the parent form) and
in the datqry (called from event procedure in the subform) and neither
worked. The query returned a blank page with just the headers and the datqyr
still gave the type mismatch error.
--
javablood


:

Open crosstab query in SQL view and add this before TRANSFORM line --
PARAMETERS [Forms]![frm_NWBA_1]![datbegin] DateTime;

:

I am trying to run two queries from a form (a Select and a Crosstab). Both
have a criteria ([Forms]![frm_NWBA_1]![datbegin]) in the date field that is
input from the user. For some unknown reason (as least to me) when I try to
run either both queries using OpenQuery in an event procedure or just the
crosstab query I get an error saying '[Forms]![frm_NWBA_1]![datbegin]' is
not recognized as a valid field name or expression. But this only happens
with the crosstab query, and if I hard code a date the crosstab query runs
just fine.

Alternatively, I have tried to code the SQL into a subform and use the
OpenQuery in an event procedure but then I get a Type Mismatch error from the
following:

Dim datqry As String

datqry = "TRANSFORM First(Hardage.RESULT) AS FirstOfRESULT " & _
"SELECT Hardage.PARAMETER, Hardage_PAR.F1_GRPORDR, " & _
"Hardage_Site_Identification.ID_Group, Hardage.STATION_ID " & _
"FROM Hardage_PAR INNER JOIN (Hardage_Site_Identification " & _
"INNER JOIN Hardage ON Hardage_Site_Identification.STATION_ID = " & _
"Hardage.STATION_ID) ON Hardage_PAR.PARAMETER = Hardage.PARAMETER " & _
"WHERE (((Hardage_Site_Identification.ID_Group) Like " * NWBA * ") " & _
"And ((Hardage.SAMPLE_DATE) >
Forms.frm_NWBA_1.datbegin.Form.cmddatreset) And ((Hardage.QC) = ""O"")) " & _
"GROUP BY Hardage.PARAMETER, Hardage_PAR.F1_GRPORDR,
Hardage_Site_Identification.ID_Group, " & _
"Hardage.STATION_ID ORDER BY Hardage_PAR.F1_GRPORDR PIVOT
Hardage.SAMPLE_DATE;"

DoCmd.OpenQuery "datqry", acViewDesign

Admittedly, I may have the relationship of Parent form and Subform mixed up
in Forms.frm_NWBA_1.datbegin.Form.cmddatreset (frm_NWBA_1 is Parent Form;
datbegin is control in Parent form, and cmddatreset is control in subform)
but I have even tried to just run the query from the subform using
[Forms]![frm_NWBA_2]![datx] in the query criteria for the date where datx is
input in the subform (just like 'datbegin' in the parent form) but I still
get the not recognized as a valid field name or expression error.

I am a 'learn as I go' type and the discussion group helps me to learn when
I have exhausted other resources but I have not been able to find my answer
through fourms, discussions, etc. Does anyone have an answer?

TIA!
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

Then you need to start removing criteria until you get some results.

Then add criteria back omitting the last one removed so you were able to get
results. Analyze what is wrong with the criteria.

Have you tried using left joins?

javablood said:
Yes. I opened the form, entered the date, then ran the query ouside of the
form and still came up blank!
--
javablood


KARL DEWEY said:
Did you run just the query, not from a form?

But you know that you need the form open and data entered into the datbegin
text box.

javablood said:
Karl,

Yes. I tried your recommendation in the code in the Event procedure in the
subform and it did not work - Type Mistmatch.

Then, separately, I tried your recommendation in a query (ctab_NWBA) that is
called from the parent form and it gave me the blank form with just headers.
At least with this one I got something this time and not the "not recognized"
error.
--
javablood


:

Have you tried to put the SQL in a query instead of the form?

:

Karl,

I tried that separately in both the query (called from the parent form) and
in the datqry (called from event procedure in the subform) and neither
worked. The query returned a blank page with just the headers and the datqyr
still gave the type mismatch error.
--
javablood


:

Open crosstab query in SQL view and add this before TRANSFORM line --
PARAMETERS [Forms]![frm_NWBA_1]![datbegin] DateTime;

:

I am trying to run two queries from a form (a Select and a Crosstab). Both
have a criteria ([Forms]![frm_NWBA_1]![datbegin]) in the date field that is
input from the user. For some unknown reason (as least to me) when I try to
run either both queries using OpenQuery in an event procedure or just the
crosstab query I get an error saying '[Forms]![frm_NWBA_1]![datbegin]' is
not recognized as a valid field name or expression. But this only happens
with the crosstab query, and if I hard code a date the crosstab query runs
just fine.

Alternatively, I have tried to code the SQL into a subform and use the
OpenQuery in an event procedure but then I get a Type Mismatch error from the
following:

Dim datqry As String

datqry = "TRANSFORM First(Hardage.RESULT) AS FirstOfRESULT " & _
"SELECT Hardage.PARAMETER, Hardage_PAR.F1_GRPORDR, " & _
"Hardage_Site_Identification.ID_Group, Hardage.STATION_ID " & _
"FROM Hardage_PAR INNER JOIN (Hardage_Site_Identification " & _
"INNER JOIN Hardage ON Hardage_Site_Identification.STATION_ID = " & _
"Hardage.STATION_ID) ON Hardage_PAR.PARAMETER = Hardage.PARAMETER " & _
"WHERE (((Hardage_Site_Identification.ID_Group) Like " * NWBA * ") " & _
"And ((Hardage.SAMPLE_DATE) >
Forms.frm_NWBA_1.datbegin.Form.cmddatreset) And ((Hardage.QC) = ""O"")) " & _
"GROUP BY Hardage.PARAMETER, Hardage_PAR.F1_GRPORDR,
Hardage_Site_Identification.ID_Group, " & _
"Hardage.STATION_ID ORDER BY Hardage_PAR.F1_GRPORDR PIVOT
Hardage.SAMPLE_DATE;"

DoCmd.OpenQuery "datqry", acViewDesign

Admittedly, I may have the relationship of Parent form and Subform mixed up
in Forms.frm_NWBA_1.datbegin.Form.cmddatreset (frm_NWBA_1 is Parent Form;
datbegin is control in Parent form, and cmddatreset is control in subform)
but I have even tried to just run the query from the subform using
[Forms]![frm_NWBA_2]![datx] in the query criteria for the date where datx is
input in the subform (just like 'datbegin' in the parent form) but I still
get the not recognized as a valid field name or expression error.

I am a 'learn as I go' type and the discussion group helps me to learn when
I have exhausted other resources but I have not been able to find my answer
through fourms, discussions, etc. Does anyone have an answer?

TIA!
Click to expand...
Click to expand...
Click to expand...
 
J

javablood

I only have 3 criteria and I removed them one by one, of course leaving the
datbegin intact, until it was only the datbegin and nothing!

No, I have not tried Left joins (really do not know how!). But the query
works with a hard code date so there must be something I am missing with
respect to the variable, passing from Parent form to Subform, declaring, or
something.

Anyway, thanks for your help!
--
javablood


KARL DEWEY said:
Then you need to start removing criteria until you get some results.

Then add criteria back omitting the last one removed so you were able to get
results. Analyze what is wrong with the criteria.

Have you tried using left joins?

javablood said:
Yes. I opened the form, entered the date, then ran the query ouside of the
form and still came up blank!
--
javablood


KARL DEWEY said:
Did you run just the query, not from a form?

But you know that you need the form open and data entered into the datbegin
text box.

:

Karl,

Yes. I tried your recommendation in the code in the Event procedure in the
subform and it did not work - Type Mistmatch.

Then, separately, I tried your recommendation in a query (ctab_NWBA) that is
called from the parent form and it gave me the blank form with just headers.
At least with this one I got something this time and not the "not recognized"
error.
--
javablood


:

Have you tried to put the SQL in a query instead of the form?

:

Karl,

I tried that separately in both the query (called from the parent form) and
in the datqry (called from event procedure in the subform) and neither
worked. The query returned a blank page with just the headers and the datqyr
still gave the type mismatch error.
--
javablood


:

Open crosstab query in SQL view and add this before TRANSFORM line --
PARAMETERS [Forms]![frm_NWBA_1]![datbegin] DateTime;

:

I am trying to run two queries from a form (a Select and a Crosstab). Both
have a criteria ([Forms]![frm_NWBA_1]![datbegin]) in the date field that is
input from the user. For some unknown reason (as least to me) when I try to
run either both queries using OpenQuery in an event procedure or just the
crosstab query I get an error saying '[Forms]![frm_NWBA_1]![datbegin]' is
not recognized as a valid field name or expression. But this only happens
with the crosstab query, and if I hard code a date the crosstab query runs
just fine.

Alternatively, I have tried to code the SQL into a subform and use the
OpenQuery in an event procedure but then I get a Type Mismatch error from the
following:

Dim datqry As String

datqry = "TRANSFORM First(Hardage.RESULT) AS FirstOfRESULT " & _
"SELECT Hardage.PARAMETER, Hardage_PAR.F1_GRPORDR, " & _
"Hardage_Site_Identification.ID_Group, Hardage.STATION_ID " & _
"FROM Hardage_PAR INNER JOIN (Hardage_Site_Identification " & _
"INNER JOIN Hardage ON Hardage_Site_Identification.STATION_ID = " & _
"Hardage.STATION_ID) ON Hardage_PAR.PARAMETER = Hardage.PARAMETER " & _
"WHERE (((Hardage_Site_Identification.ID_Group) Like " * NWBA * ") " & _
"And ((Hardage.SAMPLE_DATE) >
Forms.frm_NWBA_1.datbegin.Form.cmddatreset) And ((Hardage.QC) = ""O"")) " & _
"GROUP BY Hardage.PARAMETER, Hardage_PAR.F1_GRPORDR,
Hardage_Site_Identification.ID_Group, " & _
"Hardage.STATION_ID ORDER BY Hardage_PAR.F1_GRPORDR PIVOT
Hardage.SAMPLE_DATE;"

DoCmd.OpenQuery "datqry", acViewDesign

Admittedly, I may have the relationship of Parent form and Subform mixed up
in Forms.frm_NWBA_1.datbegin.Form.cmddatreset (frm_NWBA_1 is Parent Form;
datbegin is control in Parent form, and cmddatreset is control in subform)
but I have even tried to just run the query from the subform using
[Forms]![frm_NWBA_2]![datx] in the query criteria for the date where datx is
input in the subform (just like 'datbegin' in the parent form) but I still
get the not recognized as a valid field name or expression error.

I am a 'learn as I go' type and the discussion group helps me to learn when
I have exhausted other resources but I have not been able to find my answer
through fourms, discussions, etc. Does anyone have an answer?

TIA!
Click to expand...
Click to expand...
Click to expand...
 
S

Steve Sanford

Disclaimer: I am not an expert in SQL.

So, this is what I see:

Parameter is a reserved word (Access reserved (kb286335)
Result is a reserved word (Future SQL Server keyword (TSQL Ref in MSDN))

(See: http://allenbrowne.com/AppIssueBadWord.html)

If you can't change them, they should be surrounded with brackets, for
example

datqry = "TRANSFORM First(Hardage.[RESULT]) AS FirstOfRESULT .............


The first thing to do si add these two lines before the OpenQuery line:

Debug.Print datqry
Exit Sub


Run the procedure, then look at the Immediate window. Copy the line in the
immediate window and paste it into a text editor (Notepad). Look at the Where
clause to see if the values for the conditions are what you expect.


One problem is that VBA cannot evaluate the reference to the form/subform.
It also is a date and needs delimiters.
Another is that this part

......Like " * NWBA * ") ........

needs either single quotes or doubled double quotes. And the spaces need to
be removed.

Something like this: ......Like ""*NWBA*"" ) ........

or this: ......Like '*NWBA*') ........


According to "The Access Web" site
(http://www.mvps.org/access/forms/frm0031.htm), the way to refer to a control
on a form when you are not on a form, is like this:
Forms!Mainform!Subform1.Form!ControlName

So, using your form names, it would look like:

Forms!frm_NWBA_1!datbegin.Form!cmddatreset

where Mainform name = frm_NWBA_1,
subform name = datbegin and
control name = cmddatreset


You might try this:

'----------------------------------------------------
Dim datqry As String
Dim vSampleDate As Date ' "v" is for variable

'get the date parameter
vSampleDate = Forms!frm_NWBA_1!datbegin.Form!cmddatreset

datqry = "TRANSFORM First(Hardage.[RESULT]) AS FirstOfRESULT"

datqry = datqry & " SELECT Hardage.[PARAMETER], Hardage_PAR.F1_GRPORDR,"
datqry = datqry & " Hardage_Site_Identification.ID_Group,
Hardage.STATION_ID"

datqry = datqry & " FROM Hardage_PAR INNER JOIN
(Hardage_Site_Identification"
datqry = datqry & " INNER JOIN Hardage"
datqry = datqry & " ON Hardage_Site_Identification.STATION_ID =
Hardage.STATION_ID)"
datqry = datqry & " ON Hardage_PAR.[PARAMETER] = Hardage.[PARAMETER]"

datqry = datqry & " WHERE Hardage_Site_Identification.ID_Group Like
'*NWBA*' "
datqry = datqry & " And Hardage.SAMPLE_DATE > #" & vSampleDate & "#"
datqry = datqry & " And Hardage.QC = ""O""" '3 double quotes after the O

datqry = datqry & " GROUP BY Hardage.PARAMETER, Hardage_PAR.F1_GRPORDR,"
datqry = datqry & " Hardage_Site_Identification.ID_Group,
Hardage.STATION_ID"

datqry = datqry & "ORDER BY Hardage_PAR.F1_GRPORDR "

datqry = datqry & "PIVOT Hardage.SAMPLE_DATE;"


' Debug.Print datqry
' Exit Sub

DoCmd.OpenQuery "datqry", acViewDesign
'----------------------------------------------------


Do the Debug.Print thing to see if the SQL statement has the proper values
for the WHERE clause.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


javablood said:
I am trying to run two queries from a form (a Select and a Crosstab). Both
have a criteria ([Forms]![frm_NWBA_1]![datbegin]) in the date field that is
input from the user. For some unknown reason (as least to me) when I try to
run either both queries using OpenQuery in an event procedure or just the
crosstab query I get an error saying '[Forms]![frm_NWBA_1]![datbegin]' is
not recognized as a valid field name or expression. But this only happens
with the crosstab query, and if I hard code a date the crosstab query runs
just fine.

Alternatively, I have tried to code the SQL into a subform and use the
OpenQuery in an event procedure but then I get a Type Mismatch error from the
following:

Dim datqry As String

datqry = "TRANSFORM First(Hardage.RESULT) AS FirstOfRESULT " & _
"SELECT Hardage.PARAMETER, Hardage_PAR.F1_GRPORDR, " & _
"Hardage_Site_Identification.ID_Group, Hardage.STATION_ID " & _
"FROM Hardage_PAR INNER JOIN (Hardage_Site_Identification " & _
"INNER JOIN Hardage ON Hardage_Site_Identification.STATION_ID = " & _
"Hardage.STATION_ID) ON Hardage_PAR.PARAMETER = Hardage.PARAMETER " & _
"WHERE (((Hardage_Site_Identification.ID_Group) Like " * NWBA * ") " & _
"And ((Hardage.SAMPLE_DATE) >
Forms.frm_NWBA_1.datbegin.Form.cmddatreset) And ((Hardage.QC) = ""O"")) " & _
"GROUP BY Hardage.PARAMETER, Hardage_PAR.F1_GRPORDR,
Hardage_Site_Identification.ID_Group, " & _
"Hardage.STATION_ID ORDER BY Hardage_PAR.F1_GRPORDR PIVOT
Hardage.SAMPLE_DATE;"

DoCmd.OpenQuery "datqry", acViewDesign

Admittedly, I may have the relationship of Parent form and Subform mixed up
in Forms.frm_NWBA_1.datbegin.Form.cmddatreset (frm_NWBA_1 is Parent Form;
datbegin is control in Parent form, and cmddatreset is control in subform)
but I have even tried to just run the query from the subform using
[Forms]![frm_NWBA_2]![datx] in the query criteria for the date where datx is
input in the subform (just like 'datbegin' in the parent form) but I still
get the not recognized as a valid field name or expression error.

I am a 'learn as I go' type and the discussion group helps me to learn when
I have exhausted other resources but I have not been able to find my answer
through fourms, discussions, etc. Does anyone have an answer?

TIA!
Click to expand...
 
G

Gina Whipp

javablood,

Another thought... Karl had you use DateTime but us the field in question a
DateTime format? If it is text, that would explain why when you 'hard code'
the date it works.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

javablood said:
I only have 3 criteria and I removed them one by one, of course leaving the
datbegin intact, until it was only the datbegin and nothing!

No, I have not tried Left joins (really do not know how!). But the query
works with a hard code date so there must be something I am missing with
respect to the variable, passing from Parent form to Subform, declaring,
or
something.

Anyway, thanks for your help!
--
javablood


KARL DEWEY said:
Then you need to start removing criteria until you get some results.

Then add criteria back omitting the last one removed so you were able to
get
results. Analyze what is wrong with the criteria.

Have you tried using left joins?

javablood said:
Yes. I opened the form, entered the date, then ran the query ouside of
the
form and still came up blank!
--
javablood


:

Did you run just the query, not from a form?

But you know that you need the form open and data entered into the
datbegin
text box.

:

Karl,

Yes. I tried your recommendation in the code in the Event
procedure in the
subform and it did not work - Type Mistmatch.

Then, separately, I tried your recommendation in a query
(ctab_NWBA) that is
called from the parent form and it gave me the blank form with just
headers.
At least with this one I got something this time and not the "not
recognized"
error.
--
javablood


:

Have you tried to put the SQL in a query instead of the form?

:

Karl,

I tried that separately in both the query (called from the
parent form) and
in the datqry (called from event procedure in the subform) and
neither
worked. The query returned a blank page with just the headers
and the datqyr
still gave the type mismatch error.
--
javablood


:

Open crosstab query in SQL view and add this before TRANSFORM
line --
PARAMETERS [Forms]![frm_NWBA_1]![datbegin] DateTime;

:

I am trying to run two queries from a form (a Select and a
Crosstab). Both
have a criteria ([Forms]![frm_NWBA_1]![datbegin]) in the
date field that is
input from the user. For some unknown reason (as least to
me) when I try to
run either both queries using OpenQuery in an event
procedure or just the
crosstab query I get an error saying
'[Forms]![frm_NWBA_1]![datbegin]' is
not recognized as a valid field name or expression. But
this only happens
with the crosstab query, and if I hard code a date the
crosstab query runs
just fine.

Alternatively, I have tried to code the SQL into a subform
and use the
OpenQuery in an event procedure but then I get a Type
Mismatch error from the
following:

Dim datqry As String

datqry = "TRANSFORM First(Hardage.RESULT) AS FirstOfRESULT
" & _
"SELECT Hardage.PARAMETER, Hardage_PAR.F1_GRPORDR, "
& _
"Hardage_Site_Identification.ID_Group,
Hardage.STATION_ID " & _
"FROM Hardage_PAR INNER JOIN
(Hardage_Site_Identification " & _
"INNER JOIN Hardage ON
Hardage_Site_Identification.STATION_ID = " & _
"Hardage.STATION_ID) ON Hardage_PAR.PARAMETER =
Hardage.PARAMETER " & _
"WHERE (((Hardage_Site_Identification.ID_Group) Like
" * NWBA * ") " & _
"And ((Hardage.SAMPLE_DATE) >
Forms.frm_NWBA_1.datbegin.Form.cmddatreset) And
((Hardage.QC) = ""O"")) " & _
"GROUP BY Hardage.PARAMETER, Hardage_PAR.F1_GRPORDR,
Hardage_Site_Identification.ID_Group, " & _
"Hardage.STATION_ID ORDER BY Hardage_PAR.F1_GRPORDR
PIVOT
Hardage.SAMPLE_DATE;"

DoCmd.OpenQuery "datqry", acViewDesign

Admittedly, I may have the relationship of Parent form and
Subform mixed up
in Forms.frm_NWBA_1.datbegin.Form.cmddatreset (frm_NWBA_1
is Parent Form;
datbegin is control in Parent form, and cmddatreset is
control in subform)
but I have even tried to just run the query from the
subform using
[Forms]![frm_NWBA_2]![datx] in the query criteria for the
date where datx is
input in the subform (just like 'datbegin' in the parent
form) but I still
get the not recognized as a valid field name or expression
error.

I am a 'learn as I go' type and the discussion group helps
me to learn when
I have exhausted other resources but I have not been able
to find my answer
through fourms, discussions, etc. Does anyone have an
answer?

TIA!
Click to expand...
Click to expand...
Click to expand...
 
J

javablood

Gina,

Yes, the field 'datbegin' is date/time format. I have since put the query
in the subform and acquire 'datbegin' using [Forms]![frm_NWBA_1]![datbegin]
and setting the parameters as date/time as suggested by Karl. I have no idea
why is was not working in the Parent form though!?
--
javablood


Gina Whipp said:
javablood,

Another thought... Karl had you use DateTime but us the field in question a
DateTime format? If it is text, that would explain why when you 'hard code'
the date it works.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

javablood said:
I only have 3 criteria and I removed them one by one, of course leaving the
datbegin intact, until it was only the datbegin and nothing!

No, I have not tried Left joins (really do not know how!). But the query
works with a hard code date so there must be something I am missing with
respect to the variable, passing from Parent form to Subform, declaring,
or
something.

Anyway, thanks for your help!
--
javablood


KARL DEWEY said:
Then you need to start removing criteria until you get some results.

Then add criteria back omitting the last one removed so you were able to
get
results. Analyze what is wrong with the criteria.

Have you tried using left joins?

:

Yes. I opened the form, entered the date, then ran the query ouside of
the
form and still came up blank!
--
javablood


:

Did you run just the query, not from a form?

But you know that you need the form open and data entered into the
datbegin
text box.

:

Karl,

Yes. I tried your recommendation in the code in the Event
procedure in the
subform and it did not work - Type Mistmatch.

Then, separately, I tried your recommendation in a query
(ctab_NWBA) that is
called from the parent form and it gave me the blank form with just
headers.
At least with this one I got something this time and not the "not
recognized"
error.
--
javablood


:

Have you tried to put the SQL in a query instead of the form?

:

Karl,

I tried that separately in both the query (called from the
parent form) and
in the datqry (called from event procedure in the subform) and
neither
worked. The query returned a blank page with just the headers
and the datqyr
still gave the type mismatch error.
--
javablood


:

Open crosstab query in SQL view and add this before TRANSFORM
line --
PARAMETERS [Forms]![frm_NWBA_1]![datbegin] DateTime;

:

I am trying to run two queries from a form (a Select and a
Crosstab). Both
have a criteria ([Forms]![frm_NWBA_1]![datbegin]) in the
date field that is
input from the user. For some unknown reason (as least to
me) when I try to
run either both queries using OpenQuery in an event
procedure or just the
crosstab query I get an error saying
'[Forms]![frm_NWBA_1]![datbegin]' is
not recognized as a valid field name or expression. But
this only happens
with the crosstab query, and if I hard code a date the
crosstab query runs
just fine.

Alternatively, I have tried to code the SQL into a subform
and use the
OpenQuery in an event procedure but then I get a Type
Mismatch error from the
following:

Dim datqry As String

datqry = "TRANSFORM First(Hardage.RESULT) AS FirstOfRESULT
" & _
"SELECT Hardage.PARAMETER, Hardage_PAR.F1_GRPORDR, "
& _
"Hardage_Site_Identification.ID_Group,
Hardage.STATION_ID " & _
"FROM Hardage_PAR INNER JOIN
(Hardage_Site_Identification " & _
"INNER JOIN Hardage ON
Hardage_Site_Identification.STATION_ID = " & _
"Hardage.STATION_ID) ON Hardage_PAR.PARAMETER =
Hardage.PARAMETER " & _
"WHERE (((Hardage_Site_Identification.ID_Group) Like
" * NWBA * ") " & _
"And ((Hardage.SAMPLE_DATE) >
Forms.frm_NWBA_1.datbegin.Form.cmddatreset) And
((Hardage.QC) = ""O"")) " & _
"GROUP BY Hardage.PARAMETER, Hardage_PAR.F1_GRPORDR,
Hardage_Site_Identification.ID_Group, " & _
"Hardage.STATION_ID ORDER BY Hardage_PAR.F1_GRPORDR
PIVOT
Hardage.SAMPLE_DATE;"

DoCmd.OpenQuery "datqry", acViewDesign

Admittedly, I may have the relationship of Parent form and
Subform mixed up
in Forms.frm_NWBA_1.datbegin.Form.cmddatreset (frm_NWBA_1
is Parent Form;
datbegin is control in Parent form, and cmddatreset is
control in subform)
but I have even tried to just run the query from the
subform using
[Forms]![frm_NWBA_2]![datx] in the query criteria for the
date where datx is
input in the subform (just like 'datbegin' in the parent
form) but I still
get the not recognized as a valid field name or expression
error.

I am a 'learn as I go' type and the discussion group helps
me to learn when
I have exhausted other resources but I have not been able
to find my answer
through fourms, discussions, etc. Does anyone have an
answer?

TIA!
Click to expand...
Click to expand...
Click to expand...
 
J

javablood

Steve,

Thanks for you response. I have since put the query into the subform and
acquire 'datbegin' using [Forms]![frm_NWBA_1]![datbegin] and setting the
Parameters as Karl suggested. I have no idea why the query was not working
in the parent form though!?

I did not know 'result' was reserved. I had the double * problem with "O"
but not with "NWBA". Either way it is working noe but I will keep your
suggestion for future problems, because they are always lurking. :)
--
javablood


Steve Sanford said:
Disclaimer: I am not an expert in SQL.

So, this is what I see:

Parameter is a reserved word (Access reserved (kb286335)
Result is a reserved word (Future SQL Server keyword (TSQL Ref in MSDN))

(See: http://allenbrowne.com/AppIssueBadWord.html)

If you can't change them, they should be surrounded with brackets, for
example

datqry = "TRANSFORM First(Hardage.[RESULT]) AS FirstOfRESULT .............


The first thing to do si add these two lines before the OpenQuery line:

Debug.Print datqry
Exit Sub


Run the procedure, then look at the Immediate window. Copy the line in the
immediate window and paste it into a text editor (Notepad). Look at the Where
clause to see if the values for the conditions are what you expect.


One problem is that VBA cannot evaluate the reference to the form/subform.
It also is a date and needs delimiters.
Another is that this part

......Like " * NWBA * ") ........

needs either single quotes or doubled double quotes. And the spaces need to
be removed.

Something like this: ......Like ""*NWBA*"" ) ........

or this: ......Like '*NWBA*') ........


According to "The Access Web" site
(http://www.mvps.org/access/forms/frm0031.htm), the way to refer to a control
on a form when you are not on a form, is like this:
Forms!Mainform!Subform1.Form!ControlName

So, using your form names, it would look like:

Forms!frm_NWBA_1!datbegin.Form!cmddatreset

where Mainform name = frm_NWBA_1,
subform name = datbegin and
control name = cmddatreset


You might try this:

'----------------------------------------------------
Dim datqry As String
Dim vSampleDate As Date ' "v" is for variable

'get the date parameter
vSampleDate = Forms!frm_NWBA_1!datbegin.Form!cmddatreset

datqry = "TRANSFORM First(Hardage.[RESULT]) AS FirstOfRESULT"

datqry = datqry & " SELECT Hardage.[PARAMETER], Hardage_PAR.F1_GRPORDR,"
datqry = datqry & " Hardage_Site_Identification.ID_Group,
Hardage.STATION_ID"

datqry = datqry & " FROM Hardage_PAR INNER JOIN
(Hardage_Site_Identification"
datqry = datqry & " INNER JOIN Hardage"
datqry = datqry & " ON Hardage_Site_Identification.STATION_ID =
Hardage.STATION_ID)"
datqry = datqry & " ON Hardage_PAR.[PARAMETER] = Hardage.[PARAMETER]"

datqry = datqry & " WHERE Hardage_Site_Identification.ID_Group Like
'*NWBA*' "
datqry = datqry & " And Hardage.SAMPLE_DATE > #" & vSampleDate & "#"
datqry = datqry & " And Hardage.QC = ""O""" '3 double quotes after the O

datqry = datqry & " GROUP BY Hardage.PARAMETER, Hardage_PAR.F1_GRPORDR,"
datqry = datqry & " Hardage_Site_Identification.ID_Group,
Hardage.STATION_ID"

datqry = datqry & "ORDER BY Hardage_PAR.F1_GRPORDR "

datqry = datqry & "PIVOT Hardage.SAMPLE_DATE;"


' Debug.Print datqry
' Exit Sub

DoCmd.OpenQuery "datqry", acViewDesign
'----------------------------------------------------


Do the Debug.Print thing to see if the SQL statement has the proper values
for the WHERE clause.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


javablood said:
I am trying to run two queries from a form (a Select and a Crosstab). Both
have a criteria ([Forms]![frm_NWBA_1]![datbegin]) in the date field that is
input from the user. For some unknown reason (as least to me) when I try to
run either both queries using OpenQuery in an event procedure or just the
crosstab query I get an error saying '[Forms]![frm_NWBA_1]![datbegin]' is
not recognized as a valid field name or expression. But this only happens
with the crosstab query, and if I hard code a date the crosstab query runs
just fine.

Alternatively, I have tried to code the SQL into a subform and use the
OpenQuery in an event procedure but then I get a Type Mismatch error from the
following:

Dim datqry As String

datqry = "TRANSFORM First(Hardage.RESULT) AS FirstOfRESULT " & _
"SELECT Hardage.PARAMETER, Hardage_PAR.F1_GRPORDR, " & _
"Hardage_Site_Identification.ID_Group, Hardage.STATION_ID " & _
"FROM Hardage_PAR INNER JOIN (Hardage_Site_Identification " & _
"INNER JOIN Hardage ON Hardage_Site_Identification.STATION_ID = " & _
"Hardage.STATION_ID) ON Hardage_PAR.PARAMETER = Hardage.PARAMETER " & _
"WHERE (((Hardage_Site_Identification.ID_Group) Like " * NWBA * ") " & _
"And ((Hardage.SAMPLE_DATE) >
Forms.frm_NWBA_1.datbegin.Form.cmddatreset) And ((Hardage.QC) = ""O"")) " & _
"GROUP BY Hardage.PARAMETER, Hardage_PAR.F1_GRPORDR,
Hardage_Site_Identification.ID_Group, " & _
"Hardage.STATION_ID ORDER BY Hardage_PAR.F1_GRPORDR PIVOT
Hardage.SAMPLE_DATE;"

DoCmd.OpenQuery "datqry", acViewDesign

Admittedly, I may have the relationship of Parent form and Subform mixed up
in Forms.frm_NWBA_1.datbegin.Form.cmddatreset (frm_NWBA_1 is Parent Form;
datbegin is control in Parent form, and cmddatreset is control in subform)
but I have even tried to just run the query from the subform using
[Forms]![frm_NWBA_2]![datx] in the query criteria for the date where datx is
input in the subform (just like 'datbegin' in the parent form) but I still
get the not recognized as a valid field name or expression error.

I am a 'learn as I go' type and the discussion group helps me to learn when
I have exhausted other resources but I have not been able to find my answer
through fourms, discussions, etc. Does anyone have an answer?

TIA!
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Button on Form running a Query 4
Preventing underlying form query from auto refreshing/requerying? 2
Help! Suck on copy data from form 1
problem in a form from crosstab query 2
Crosstab query on a Form 1
Best way to calc total on a form from a bunch of subforms 3
Crosstab form based on Crosstab query? 1
form value based on query 3

Top