Need help with query linked to form.

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

Hi, I am trying to run a query based off of an unbound field in a form.
Essentially I would like the end user to input a date in the form and hit the
command button to run the query where the date would cross over to the query
criteria. I have done this in access 2000 but since upgrading to 2007 it
doesn't seem to be working. I have my date feild formated as yyyy-mm-dd in
both my table and my form so they should match? Any help on this would be
much appreciated. Thanks
 
So your query contains something like:
[Forms].[Form1].[Text0]

To ensure JET understands this correctly:

a) Declare the parameter in the query.
In query design, choose Parameters on the Query Design tab of the ribbon
(Show/Hide chunk.) Access pops up a dialolg. Enter:
[Forms].[Form1].[Text0] Date/Time

b) Set the Format property of the text box on the form.
Use a date format, such as Short Date or yyyy\-mm\-dd.
This is how you indicate the data type of an unbound text box.
You should now find that Access rejects 2008-02-31 as a bad entry.

c) Make sure you move out of Text0 before running the query.
Access doesn't update the Value of a text box until you move the focus to
some other control.
 
Allen thank you for that info. I think it is what I needed but I am now
getting an error stating that "This expression is typed incorrectly, or it is
too complex to be evaluated. For example, a numeric expression may contain
too many compliated elements. Try simplifying the expression by assigning
parts of the expression to variables." I am not sure what this means. Let me
know if the SQl would help. Thanks

Allen Browne said:
So your query contains something like:
[Forms].[Form1].[Text0]

To ensure JET understands this correctly:

a) Declare the parameter in the query.
In query design, choose Parameters on the Query Design tab of the ribbon
(Show/Hide chunk.) Access pops up a dialolg. Enter:
[Forms].[Form1].[Text0] Date/Time

b) Set the Format property of the text box on the form.
Use a date format, such as Short Date or yyyy\-mm\-dd.
This is how you indicate the data type of an unbound text box.
You should now find that Access rejects 2008-02-31 as a bad entry.

c) Make sure you move out of Text0 before running the query.
Access doesn't update the Value of a text box until you move the focus to
some other control.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Dan said:
Hi, I am trying to run a query based off of an unbound field in a form.
Essentially I would like the end user to input a date in the form and hit
the
command button to run the query where the date would cross over to the
query
criteria. I have done this in access 2000 but since upgrading to 2007 it
doesn't seem to be working. I have my date feild formated as yyyy-mm-dd in
both my table and my form so they should match? Any help on this would be
much appreciated. Thanks
Click to expand...
Click to expand...
 
Yes: post the SQL please.

That message just means Access can't parse the SQL statement. It can be 'too
complex' (e.g. too many ANDs or UNIONs), but it can also be other things
like bad data types, or reserved words used as field names.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Dan said:
Allen thank you for that info. I think it is what I needed but I am now
getting an error stating that "This expression is typed incorrectly, or it
is
too complex to be evaluated. For example, a numeric expression may contain
too many compliated elements. Try simplifying the expression by assigning
parts of the expression to variables." I am not sure what this means. Let
me
know if the SQl would help. Thanks

Allen Browne said:
So your query contains something like:
[Forms].[Form1].[Text0]

To ensure JET understands this correctly:

a) Declare the parameter in the query.
In query design, choose Parameters on the Query Design tab of the ribbon
(Show/Hide chunk.) Access pops up a dialolg. Enter:
[Forms].[Form1].[Text0] Date/Time

b) Set the Format property of the text box on the form.
Use a date format, such as Short Date or yyyy\-mm\-dd.
This is how you indicate the data type of an unbound text box.
You should now find that Access rejects 2008-02-31 as a bad entry.

c) Make sure you move out of Text0 before running the query.
Access doesn't update the Value of a text box until you move the focus to
some other control.

Dan said:
Hi, I am trying to run a query based off of an unbound field in a form.
Essentially I would like the end user to input a date in the form and
hit
the
command button to run the query where the date would cross over to the
query
criteria. I have done this in access 2000 but since upgrading to 2007
it
doesn't seem to be working. I have my date feild formated as yyyy-mm-dd
in
both my table and my form so they should match? Any help on this would
be
much appreciated. Thanks
Click to expand...
Click to expand...
Click to expand...
 
Here is the SQL.

PARAMETERS [Forms]![Open Po - f]![Query date] DateTime;
SELECT [PO data-t].[HDSTH#], [PO data-t].[HDPO#], [PO data-t].HDVID, [PO
data-t].VXDNAM, [PO data-t].HDODAT, [PO data-t].HDDDAT, [PO data-t].HDBUYR,
[PO data-t].[LNLN#], [PO data-t].LNITEM, [PO data-t].LNDESC, [PO
data-t].LNVCAT, [PO data-t].[LNGL#], [PO data-t].LNLCID, [PO data-t].LNOQTY,
[PO data-t].LNRQTY, [PO data-t].LNUNIT, [PO data-t].LNUPRC, [PO
data-t].LNXLNT, [PO data-t].LNDDAT, [PO data-t].LNTYPE, [PO data-t].HDRSTS,
[PO data-t].LNRSTS
FROM [PO data-t]
WHERE ((([PO data-t].HDODAT)=[Forms]![Open Po - f]![Query date]));


Allen Browne said:
Yes: post the SQL please.

That message just means Access can't parse the SQL statement. It can be 'too
complex' (e.g. too many ANDs or UNIONs), but it can also be other things
like bad data types, or reserved words used as field names.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Dan said:
Allen thank you for that info. I think it is what I needed but I am now
getting an error stating that "This expression is typed incorrectly, or it
is
too complex to be evaluated. For example, a numeric expression may contain
too many compliated elements. Try simplifying the expression by assigning
parts of the expression to variables." I am not sure what this means. Let
me
know if the SQl would help. Thanks

Allen Browne said:
So your query contains something like:
[Forms].[Form1].[Text0]

To ensure JET understands this correctly:

a) Declare the parameter in the query.
In query design, choose Parameters on the Query Design tab of the ribbon
(Show/Hide chunk.) Access pops up a dialolg. Enter:
[Forms].[Form1].[Text0] Date/Time

b) Set the Format property of the text box on the form.
Use a date format, such as Short Date or yyyy\-mm\-dd.
This is how you indicate the data type of an unbound text box.
You should now find that Access rejects 2008-02-31 as a bad entry.

c) Make sure you move out of Text0 before running the query.
Access doesn't update the Value of a text box until you move the focus to
some other control.

Hi, I am trying to run a query based off of an unbound field in a form.
Essentially I would like the end user to input a date in the form and
hit
the
command button to run the query where the date would cross over to the
query
criteria. I have done this in access 2000 but since upgrading to 2007
it
doesn't seem to be working. I have my date feild formated as yyyy-mm-dd
in
both my table and my form so they should match? Any help on this would
be
much appreciated. Thanks
Click to expand...
Click to expand...
Click to expand...
 
That should be okay.

If you open the table named PO data-t in design view, what is the Data Type
of the HDODAT column? I am assuming it is Date/Time (not Text or Number.)

Try:
PARAMETERS [Forms]![Open Po - f]![Query date] DateTime;
SELECT [PO data-t].*
FROM [PO data-t]
WHERE HDODAT = [Forms]![Open Po - f]![Query date];

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Dan said:
Here is the SQL.
Click to expand...
PARAMETERS [Forms]![Open Po - f]![Query date] DateTime;
SELECT [PO data-t].[HDSTH#],
[PO data-t].[HDPO#],
[PO data-t].HDVID,
[PO data-t].VXDNAM,
[PO data-t].HDODAT,
[PO data-t].HDDDAT,
[PO data-t].HDBUYR,
[PO data-t].[LNLN#],
[PO data-t].LNITEM,
[PO data-t].LNDESC,
[PO data-t].LNVCAT,
[PO data-t].[LNGL#],
[PO data-t].LNLCID,
[PO data-t].LNOQTY,
[PO data-t].LNRQTY,
[PO data-t].LNUNIT,
[PO data-t].LNUPRC,
[PO data-t].LNXLNT,
[PO data-t].LNDDAT,
[PO data-t].LNTYPE,
[PO data-t].HDRSTS,
[PO data-t].LNRSTS
FROM [PO data-t]
WHERE ((([PO data-t].HDODAT)=[Forms]![Open Po - f]![Query date]));
 
OK, I think this is the problem when I loked at the table it was in fact set
to Text. So now a new question. I am pulling this data via ODBC and for the
dates that are brought in they default to text. Is there an easy or quick way
to make this feild change or default to Date format when I pull in the table?

Allen Browne said:
That should be okay.

If you open the table named PO data-t in design view, what is the Data Type
of the HDODAT column? I am assuming it is Date/Time (not Text or Number.)

Try:
PARAMETERS [Forms]![Open Po - f]![Query date] DateTime;
SELECT [PO data-t].*
FROM [PO data-t]
WHERE HDODAT = [Forms]![Open Po - f]![Query date];

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Dan said:
Here is the SQL.
Click to expand...
PARAMETERS [Forms]![Open Po - f]![Query date] DateTime;
SELECT [PO data-t].[HDSTH#],
[PO data-t].[HDPO#],
[PO data-t].HDVID,
[PO data-t].VXDNAM,
[PO data-t].HDODAT,
[PO data-t].HDDDAT,
[PO data-t].HDBUYR,
[PO data-t].[LNLN#],
[PO data-t].LNITEM,
[PO data-t].LNDESC,
[PO data-t].LNVCAT,
[PO data-t].[LNGL#],
[PO data-t].LNLCID,
[PO data-t].LNOQTY,
[PO data-t].LNRQTY,
[PO data-t].LNUNIT,
[PO data-t].LNUPRC,
[PO data-t].LNXLNT,
[PO data-t].LNDDAT,
[PO data-t].LNTYPE,
[PO data-t].HDRSTS,
[PO data-t].LNRSTS
FROM [PO data-t]
WHERE ((([PO data-t].HDODAT)=[Forms]![Open Po - f]![Query date]));
Click to expand...
 
How are you, 'pulling the table'?
Is it linked or imported?
If imported, do you use a Make Table query? An Append query? Or
TransferDatabase? Or ...?

The data type is the problem: Access is not making sense of applying the
date/time parameter to the Text field.

If you can't import it propertly, it may be possible to use a query to
generate a date/time field, using CVDate(), or Left(), Mid(), and Right() to
parse the values and DateSerial() to make a true date. Getting the data type
right in the table would be preferable though.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Dan said:
OK, I think this is the problem when I loked at the table it was in fact
set
to Text. So now a new question. I am pulling this data via ODBC and for
the
dates that are brought in they default to text. Is there an easy or quick
way
to make this feild change or default to Date format when I pull in the
table?

Allen Browne said:
That should be okay.

If you open the table named PO data-t in design view, what is the Data
Type
of the HDODAT column? I am assuming it is Date/Time (not Text or Number.)

Try:
PARAMETERS [Forms]![Open Po - f]![Query date] DateTime;
SELECT [PO data-t].*
FROM [PO data-t]
WHERE HDODAT = [Forms]![Open Po - f]![Query date];

Dan said:
Here is the SQL.
Click to expand...
PARAMETERS [Forms]![Open Po - f]![Query date] DateTime;
SELECT [PO data-t].[HDSTH#],
[PO data-t].[HDPO#],
[PO data-t].HDVID,
[PO data-t].VXDNAM,
[PO data-t].HDODAT,
[PO data-t].HDDDAT,
[PO data-t].HDBUYR,
[PO data-t].[LNLN#],
[PO data-t].LNITEM,
[PO data-t].LNDESC,
[PO data-t].LNVCAT,
[PO data-t].[LNGL#],
[PO data-t].LNLCID,
[PO data-t].LNOQTY,
[PO data-t].LNRQTY,
[PO data-t].LNUNIT,
[PO data-t].LNUPRC,
[PO data-t].LNXLNT,
[PO data-t].LNDDAT,
[PO data-t].LNTYPE,
[PO data-t].HDRSTS,
[PO data-t].LNRSTS
FROM [PO data-t]
WHERE ((([PO data-t].HDODAT)=[Forms]![Open Po - f]![Query date]));
Yes: post the SQL please.

Allen thank you for that info. I think it is what I needed but I am
now
now getting an error stating that "This expression is typed
incorrectly,
or it is too complex to be evaluated.
Click to expand...
Click to expand...
Click to expand...
 
I am doing a link table than doing a make table query to create the table
that I am querying off of for my form. So I could try to parse them out and
than re-parse them back together but was hoping there was an easier way with
fewer steps. I did try in my query to use the Format(date, yyyy-mm-dd") but
it didn't seem to make a differnce it still looked at it as a text line. I
also tried to just input Short Date under the qeury format and that didn't
work either. I have never used the CVDate() function though. What would you
suggest?


Allen Browne said:
How are you, 'pulling the table'?
Is it linked or imported?
If imported, do you use a Make Table query? An Append query? Or
TransferDatabase? Or ...?

The data type is the problem: Access is not making sense of applying the
date/time parameter to the Text field.

If you can't import it propertly, it may be possible to use a query to
generate a date/time field, using CVDate(), or Left(), Mid(), and Right() to
parse the values and DateSerial() to make a true date. Getting the data type
right in the table would be preferable though.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Dan said:
OK, I think this is the problem when I loked at the table it was in fact
set
to Text. So now a new question. I am pulling this data via ODBC and for
the
dates that are brought in they default to text. Is there an easy or quick
way
to make this feild change or default to Date format when I pull in the
table?

Allen Browne said:
That should be okay.

If you open the table named PO data-t in design view, what is the Data
Type
of the HDODAT column? I am assuming it is Date/Time (not Text or Number.)

Try:
PARAMETERS [Forms]![Open Po - f]![Query date] DateTime;
SELECT [PO data-t].*
FROM [PO data-t]
WHERE HDODAT = [Forms]![Open Po - f]![Query date];

Here is the SQL.

PARAMETERS [Forms]![Open Po - f]![Query date] DateTime;
SELECT [PO data-t].[HDSTH#],
[PO data-t].[HDPO#],
[PO data-t].HDVID,
[PO data-t].VXDNAM,
[PO data-t].HDODAT,
[PO data-t].HDDDAT,
[PO data-t].HDBUYR,
[PO data-t].[LNLN#],
[PO data-t].LNITEM,
[PO data-t].LNDESC,
[PO data-t].LNVCAT,
[PO data-t].[LNGL#],
[PO data-t].LNLCID,
[PO data-t].LNOQTY,
[PO data-t].LNRQTY,
[PO data-t].LNUNIT,
[PO data-t].LNUPRC,
[PO data-t].LNXLNT,
[PO data-t].LNDDAT,
[PO data-t].LNTYPE,
[PO data-t].HDRSTS,
[PO data-t].LNRSTS
FROM [PO data-t]
WHERE ((([PO data-t].HDODAT)=[Forms]![Open Po - f]![Query date]));

Yes: post the SQL please.

Allen thank you for that info. I think it is what I needed but I am
now
now getting an error stating that "This expression is typed
incorrectly,
or it is too complex to be evaluated.
Click to expand...
Click to expand...
Click to expand...
 
Format() is guaranteed to create a Text field.
CVDate() might work (or even CDate() if there are no nulls.)

You generally do need to parse the dates so Access knows how to interpret
them.
 

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

Formatting date gives bad results in query 2
Parameter Queries in Crosstab Query 2
extract just month/year from table field?? 2
Delete Query 6
crosstab query with datea parameters 2
Date Function in query 3
date mismatch in query..do not know how to solve 3
query on date 1

Back
Top