Null entry to extract all records

[PsyberFox]

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

 

[Douglas J. Steele]

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.

 

[PsyberFox]

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.

--
Doug Steele, Microsoft Access MVP

(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
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

 

[Douglas J. Steele]

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.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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.

--
Doug Steele, Microsoft Access MVP

(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
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

 

[PsyberFox]

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

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.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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.

--
Doug Steele, Microsoft Access MVP

(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
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

 

[Douglas J. Steele]

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)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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

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.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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.

--
Doug Steele, Microsoft Access MVP

(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
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

 

[PsyberFox]

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.

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)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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

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.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


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.

--
Doug Steele, Microsoft Access MVP

(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
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

 

[Douglas J. Steele]

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?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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.

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)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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

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.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


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.

--
Doug Steele, Microsoft Access MVP

(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
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

 

[PsyberFox]

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

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?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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.

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)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


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

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.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


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.

--
Doug Steele, Microsoft Access MVP

(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
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

 

[Douglas J. Steele]

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.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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

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?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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.

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)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


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

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.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


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.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
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

 

[PsyberFox]

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.

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.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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

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?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


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.

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)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


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

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.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


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.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
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