Null entry to extract all records

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

PsyberFox - 17 Mar 2008 15:51 GMT

I have read a lot of literature on how to extract records in a query, based
on input from a form, where certain fields are left blank as an indication of
wanting all the options from that specific field. It goes something to the
effect of criteria: [Forms]![Form]![Field] or Like [Forms]![Form]![Field] is
null, but I get an ODBC Call Failed error when trying to do this... it saves
the query no problem, but when running it produces this error...

Pse help

Reply to this Message

Douglas J. Steele - 17 Mar 2008 16:20 GMT

Switch your query to the SQL view (you can do this through the View menu),
and copy-and-paste the actual SQL that's being generated.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

>I have read a lot of literature on how to extract records in a query, based
> on input from a form, where certain fields are left blank as an indication
[quoted text clipped - 7 lines]
>
> Pse help

Reply to this Message

PsyberFox - 18 Mar 2008 09:46 GMT

This is the QA syntax:
select    [Date], [Month], [Year], MachNo, MachModel, Shift,
    ShiftPeriod, [Time], Style, [Size], Quantity,
    [8hrTarget], Target, Operator, Technician
from    Prod_Knitting_Data_1
where    ([Date] >= '03/15/2008' and [Date] <= '03/17/2008')
    and (MachNo = '57' or Like MachNo is null)

but it still gives a syntax error...

W

> Switch your query to the SQL view (you can do this through the View menu),
> and copy-and-paste the actual SQL that's being generated.
[quoted text clipped - 10 lines]
> >
> > Pse help

Reply to this Message

Douglas J. Steele - 18 Mar 2008 11:54 GMT

Remove the word Like in

and (MachNo = '57' or Like MachNo is null)

What DBMS are you going against? Different DBMS have different requirements
for dates (for instance, if going against a Jet database, you must delimit
the dates with #, not '), so that may be a source of problems too.

And just a comment. You really should avoid using reserved words for field
names. At least you've put square brackets around them, but it's really much
better to use different names.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> This is the QA syntax:
> select [Date], [Month], [Year], MachNo, MachModel, Shift,
[quoted text clipped - 27 lines]
>> >
>> > Pse help

Reply to this Message

PsyberFox - 18 Mar 2008 15:37 GMT

Hi,

I realise that certain names are reserved...

The MachNo is a nchar field referring to a machine number. And when I remove
the word "like" then it doesn't extract any records when the field is left
blank by the user. When the '57' is selected, it only extracts records for
machine number 57, but not all records for a specified date when left blank.

> Remove the word Like in
>
[quoted text clipped - 39 lines]
> >> >
> >> > Pse help

Reply to this Message

Douglas J. Steele - 18 Mar 2008 15:58 GMT

Like MachNo is null isn't valid SQL.

As your SQL is currently written, it doesn't make sense to expect all
records for a specified date because you've hard-coded MachNo 57 into the
query.

Since you appear to be using a pass-through query (otherwise, it's incorrect
to use single quotes around the dates), it's not possible to have a
parameter prompt you to enter a machine number, or nothing to get all. If
it's not a pass-through query, try something like:

Select [Date], [Month], [Year], MachNo, MachModel, Shift,
ShiftPeriod, [Time], Style, [Size], Quantity,
[8hrTarget], Target, Operator, Technician
from Prod_Knitting_Data_1
where ([Date] >= #03/15/2008# and [Date] <= #03/17/2008#)
and (MachNo = [What Machine No?] or [What Machine No?] is null)

You can also have the query refer to a control on an open form, rather than
pop up a prompt, but again, that won't work with pass-through queries:

Select [Date], [Month], [Year], MachNo, MachModel, Shift,
ShiftPeriod, [Time], Style, [Size], Quantity,
[8hrTarget], Target, Operator, Technician
from Prod_Knitting_Data_1
where ([Date] >= #03/15/2008# and [Date] <= #03/17/2008#)
and (MachNo = Forms![NameOfForm]![NameOfControl] or
Forms![NameOfForm]![NameOfControl] is null)

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Hi,
>
[quoted text clipped - 56 lines]
>> >> >
>> >> > Pse help

Reply to this Message

PsyberFox - 19 Mar 2008 07:40 GMT

Hi Douglas,

I think we're missing each other a bit... this query is merely a shortened
version of this whole thing running from within MSA Forms... A form gets the
fromdate and todate and then passes this to a query which is supposed to
extract records based on the dates, a machine number and one other field.
However, it was recommended to me on some other Access help site to put like
[field] is null to extract all records between the two dates where the
machine number is left blank. I merely shortened this into Query Analyser to
see if it would work, and obviously it didn't. If I use the or [field] is
null then it doesn't extract any records if the machine number is left
blank... and that's where I am at the mo... thank you kindly for your
assistance in this matter.

> Like MachNo is null isn't valid SQL.
>
[quoted text clipped - 85 lines]
> >> >> >
> >> >> > Pse help

Reply to this Message

Douglas J. Steele - 19 Mar 2008 11:58 GMT

Okay, are you saying that

select [Date], [Month], [Year], MachNo, MachModel, Shift,
ShiftPeriod, [Time], Style, [Size], Quantity,
[8hrTarget], Target, Operator, Technician
from Prod_Knitting_Data_1
where ([Date] >= '03/15/2008' and [Date] <= '03/17/2008')
and (MachNo = '57' or Like MachNo is null)

is or is not your actual SQL?

If it is your actual SQL, it's invalid SQL, and I've shown you how to at
least make it valid SQL.

If it's not your actual SQL, how do you expect me to help you?

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Hi Douglas,
>
[quoted text clipped - 110 lines]
>> >> >> >
>> >> >> > Pse help

Reply to this Message

PsyberFox - 19 Mar 2008 12:11 GMT

This is the SQL view in MSA:

SELECT [Date], [Month], [Year], MachNo, MachModel, Shift, ShiftPeriod,
[Time], Style, [Size], Quantity, [8hrTarget], Target, Operator, Technician
FROM dbo_Prod_Knitting_Data_1
WHERE ([Date]>=Forms!frm_Enq_Knit_Input!txtDateFrom And
[Date]<=Forms!frm_Enq_Knit_Input!txtDateTo) And
(MachNo=Forms!frm_Enq_Knit_Input!cboMachNo Or
Forms!frm_Enq_Knit_Input!cboMachNo Is Null) And
(Shift=Forms!frm_Enq_Knit_Input!lstShift Or Forms!frm_Enq_Knit_Input!lstShift
Is Null);

> Okay, are you saying that
>
[quoted text clipped - 126 lines]
> >> >> >> >
> >> >> >> > Pse help

Reply to this Message

Douglas J. Steele - 19 Mar 2008 12:32 GMT

I don't see any reason why that shouldn't work.

I'm assuming that cboMachNo is a combo box, and when you say "the machine
number is left blank", you mean that nothing is selected in the combo box,
as opposed to having created an entry in the combo box that's a blank and
you're selecting that one.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> This is the SQL view in MSA:
>
[quoted text clipped - 155 lines]
>> >> >> >> >
>> >> >> >> > Pse help

Reply to this Message

PsyberFox - 19 Mar 2008 12:41 GMT

You're assuming correct... I'm going to start this query from afresh and see
if I didn't miss something else... Thank you so much for your assistance...

> I don't see any reason why that shouldn't work.
>
[quoted text clipped - 162 lines]
> >> >> >> >> >
> >> >> >> >> > Pse help

Reply to this Message