Re: Need help with query linked to form.

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dan" <Dan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:58FBE3BA-A3FA-416A-97AE-AAFE7EA49F22@xxxxxxxxxxxxxxxx
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" wrote:

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" <Dan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2D7E3F1B-AB4E-4A90-B1AA-DD8D10AD3C6E@xxxxxxxxxxxxxxxx
> 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.

.

Relevant Pages

  • Re: Syntax error DateDiff
    ... SidsteOfStatusændringDato is a date taken from a query that selects ... There are no other calculated controls in the form. ... what data type is this field when you open the table in design view? ... Allen Browne - Microsoft MVP. ...
    (microsoft.public.access.formscoding)
  • Re: Syntax error DateDiff
    ... Allen Browne - Microsoft MVP. ... SidsteOfStatusændringDato is a date taken from a query that selects ... There are no other calculated controls in the form. ... what data type is this field when you open the table in design view? ...
    (microsoft.public.access.formscoding)
  • Re: Need help with query linked to form.
    ... I think this is the problem when I loked at the table it was in fact set ... If you open the table named PO data-t in design view, what is the Data Type ... Allen Browne - Microsoft MVP. ...
    (microsoft.public.access.queries)
  • Re: Parameterized query
    ... Allen Browne wrote: ... The nearest you could get would be to substitute yesterday's date if the ... In query design, enter an expression like this into the Criteria row ... parameter name, and specify the data type, i.e.: ...
    (microsoft.public.access.queries)
  • Re: Need help with query linked to form.
    ... I am doing a link table than doing a make table query to create the table ... The data type is the problem: Access is not making sense of applying the ... date/time parameter to the Text field. ... If you open the table named PO data-t in design view, ...
    (microsoft.public.access.queries)