Solve query?

  • Thread starter Thread starter jo
  • Start date Start date
J

jo

This is my SQL view and i would like my query to also show wot [channel] has
not been entered is this possible? the user would enter a number of a channel
into the[Channel] field. The query shows the records of the last 15 days.

SELECT [Enter Talyform Data Query].[Date:], [Enter Talyform Data
Query].TodaysDate, [Enter Talyform Data Query].Channel, [Enter Talyform Data
Query].[Enter Talyform Data].Type, [Enter Talyform Data Query].[Inspection
By], [Enter Talyform Data Query].[First Off], [Enter Talyform Data
Query].Setting, [Enter Talyform Data Query].[Routine Check], [Enter Talyform
Data Query].Grinding, [Enter Talyform Data Query].Honed, [Enter Talyform Data
Query].RingAppr, [Enter Talyform Data Query].[2ringchk]
FROM [Enter Talyform Data Query]
WHERE ((([Enter Talyform Data Query].[Date:])>Date()-15));
 
I'm having trouble understanding the question. Could you show an example of
what you need?

Also this looks strange:
[Enter Talyform Data Query].[Enter Talyform Data].Type,

What is the Type for? Is it a typo?
 
Hi Jerry, I shall try and make this short!!
Type is a Bearing designation. Channel is a production line. Date: is the
date when entered.
Type Channel Date:
22210cc PC6 10/09/2009
22212cc Pc8 12/09/2009
22310cc pc4 21/09/2009
23218cc pc7 23/09/2009
The query I have at the moment will show the above of the last 15 days.
I want a query to show the last 15 days and also PC2 [Channel] was not
entered. is this possible?

Jerry Whittle said:
I'm having trouble understanding the question. Could you show an example of
what you need?

Also this looks strange:
[Enter Talyform Data Query].[Enter Talyform Data].Type,

What is the Type for? Is it a typo?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


jo said:
This is my SQL view and i would like my query to also show wot [channel] has
not been entered is this possible? the user would enter a number of a channel
into the[Channel] field. The query shows the records of the last 15 days.

SELECT [Enter Talyform Data Query].[Date:], [Enter Talyform Data
Query].TodaysDate, [Enter Talyform Data Query].Channel, [Enter Talyform Data
Query].[Enter Talyform Data].Type, [Enter Talyform Data Query].[Inspection
By], [Enter Talyform Data Query].[First Off], [Enter Talyform Data
Query].Setting, [Enter Talyform Data Query].[Routine Check], [Enter Talyform
Data Query].Grinding, [Enter Talyform Data Query].Honed, [Enter Talyform Data
Query].RingAppr, [Enter Talyform Data Query].[2ringchk]
FROM [Enter Talyform Data Query]
WHERE ((([Enter Talyform Data Query].[Date:])>Date()-15));
Click to expand...
Click to expand...
 
Hi Jerry, I shall try and make this short!!
Type is a Bearing designation. Channel is a production line. Date: is the
date when entered.
Type Channel Date:
22210cc PC6 10/09/2009
22212cc Pc8 12/09/2009
22310cc pc4 21/09/2009
23218cc pc7 23/09/2009
The query I have at the moment will show the above of the last 15 days.
I want a query to show the last 15 days and also PC2 [Channel] was not
entered. is this possible?
Click to expand...

I presume you have a table of all valid Channel values? If not, then you could
be stuck saying that PC31 and PQ9341234AAcD54 were not entered...

Given such a table, an "Unmatched" query will work:

SELECT Channels.Channel
FROM Channel
WHERE NOT EXISTS(
SELECT Channel FROM yourtable
WHERE [Date] BETWEEN DateAdd('d", -15, Date()) AND Date()
 
Hi John I have created a table for the Channels and named it Channels. I have
created a new query and pasted your SQL but I get a syntax error? can you
help plz..
SELECT Channels.Channel
FROM Channel
WHERE NOT EXISTS(
SELECT Channel FROM Enter Talyform Data
WHERE [Date:] BETWEEN DateAdd('d", -15, Date()) AND Date()


John W. Vinson said:
Hi Jerry, I shall try and make this short!!
Type is a Bearing designation. Channel is a production line. Date: is the
date when entered.
Type Channel Date:
22210cc PC6 10/09/2009
22212cc Pc8 12/09/2009
22310cc pc4 21/09/2009
23218cc pc7 23/09/2009
The query I have at the moment will show the above of the last 15 days.
I want a query to show the last 15 days and also PC2 [Channel] was not
entered. is this possible?
Click to expand...

I presume you have a table of all valid Channel values? If not, then you could
be stuck saying that PC31 and PQ9341234AAcD54 were not entered...

Given such a table, an "Unmatched" query will work:

SELECT Channels.Channel
FROM Channel
WHERE NOT EXISTS(
SELECT Channel FROM yourtable
WHERE [Date] BETWEEN DateAdd('d", -15, Date()) AND Date()
Click to expand...
 
If your table's named Enter Talyform Data, you need to use square brackets
due to the space:

SELECT Channels.Channel
FROM Channel
WHERE NOT EXISTS(
SELECT Channel FROM [Enter Talyform Data]
WHERE [Date] BETWEEN DateAdd('d", -15, Date()) AND Date()

Note that I also removed the semicolon from the field name Date in the Where
clause. As far as I know, field names cannot contain colons. If your intent
is to prompt the user to enter a date, that subquery is pretty meaningless.

Incidentally, you should consider renaming your field name. Date is a
reserved word, and you should never use reserved words for your own
purposes. For a comprehensive list of names to avoid (as well as a link to a
free utility to check your application for compliance), see what Allen
Browne has at http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



jo said:
Hi John I have created a table for the Channels and named it Channels. I
have
created a new query and pasted your SQL but I get a syntax error? can you
help plz..
SELECT Channels.Channel
FROM Channel
WHERE NOT EXISTS(
SELECT Channel FROM Enter Talyform Data
WHERE [Date:] BETWEEN DateAdd('d", -15, Date()) AND Date()


John W. Vinson said:
Hi Jerry, I shall try and make this short!!
Type is a Bearing designation. Channel is a production line. Date: is
the
date when entered.
Type Channel Date:
22210cc PC6 10/09/2009
22212cc Pc8 12/09/2009
22310cc pc4 21/09/2009
23218cc pc7 23/09/2009
The query I have at the moment will show the above of the last 15 days.
I want a query to show the last 15 days and also PC2 [Channel] was not
entered. is this possible?
Click to expand...

I presume you have a table of all valid Channel values? If not, then you
could
be stuck saying that PC31 and PQ9341234AAcD54 were not entered...

Given such a table, an "Unmatched" query will work:

SELECT Channels.Channel
FROM Channel
WHERE NOT EXISTS(
SELECT Channel FROM yourtable
WHERE [Date] BETWEEN DateAdd('d", -15, Date()) AND Date()
Click to expand...
Click to expand...
 
Hi Doug thanks for your help but I still get a syntax error.. I'm not sure
wot this is 'd"? My date has a semi-colon Date: but I have tried to add that
but no joy??

Douglas J. Steele said:
If your table's named Enter Talyform Data, you need to use square brackets
due to the space:

SELECT Channels.Channel
FROM Channel
WHERE NOT EXISTS(
SELECT Channel FROM [Enter Talyform Data]
WHERE [Date] BETWEEN DateAdd('d", -15, Date()) AND Date()

Note that I also removed the semicolon from the field name Date in the Where
clause. As far as I know, field names cannot contain colons. If your intent
is to prompt the user to enter a date, that subquery is pretty meaningless.

Incidentally, you should consider renaming your field name. Date is a
reserved word, and you should never use reserved words for your own
purposes. For a comprehensive list of names to avoid (as well as a link to a
free utility to check your application for compliance), see what Allen
Browne has at http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



jo said:
Hi John I have created a table for the Channels and named it Channels. I
have
created a new query and pasted your SQL but I get a syntax error? can you
help plz..
SELECT Channels.Channel
FROM Channel
WHERE NOT EXISTS(
SELECT Channel FROM Enter Talyform Data
WHERE [Date:] BETWEEN DateAdd('d", -15, Date()) AND Date()


John W. Vinson said:
Hi Jerry, I shall try and make this short!!
Type is a Bearing designation. Channel is a production line. Date: is
the
date when entered.
Type Channel Date:
22210cc PC6 10/09/2009
22212cc Pc8 12/09/2009
22310cc pc4 21/09/2009
23218cc pc7 23/09/2009
The query I have at the moment will show the above of the last 15 days.
I want a query to show the last 15 days and also PC2 [Channel] was not
entered. is this possible?

I presume you have a table of all valid Channel values? If not, then you
could
be stuck saying that PC31 and PQ9341234AAcD54 were not entered...

Given such a table, an "Unmatched" query will work:

SELECT Channels.Channel
FROM Channel
WHERE NOT EXISTS(
SELECT Channel FROM yourtable
WHERE [Date] BETWEEN DateAdd('d", -15, Date()) AND Date()
Click to expand...
Click to expand...
Click to expand...
 
Sorry: copy-and-paste error. You need double-quotes both before and after
the d, not a single quote in front and a double quote afterwards. It's what
tells the DateAdd function to subtract 15 days from the current date.

SELECT Channels.Channel
FROM Channel
WHERE NOT EXISTS(
SELECT Channel FROM [Enter Talyform Data]
WHERE [Date:] BETWEEN DateAdd("d", -15, Date()) AND Date()

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



jo said:
Hi Doug thanks for your help but I still get a syntax error.. I'm not sure
wot this is 'd"? My date has a semi-colon Date: but I have tried to add
that
but no joy??

Douglas J. Steele said:
If your table's named Enter Talyform Data, you need to use square
brackets
due to the space:

SELECT Channels.Channel
FROM Channel
WHERE NOT EXISTS(
SELECT Channel FROM [Enter Talyform Data]
WHERE [Date] BETWEEN DateAdd('d", -15, Date()) AND Date()

Note that I also removed the semicolon from the field name Date in the
Where
clause. As far as I know, field names cannot contain colons. If your
intent
is to prompt the user to enter a date, that subquery is pretty
meaningless.

Incidentally, you should consider renaming your field name. Date is a
reserved word, and you should never use reserved words for your own
purposes. For a comprehensive list of names to avoid (as well as a link
to a
free utility to check your application for compliance), see what Allen
Browne has at http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



jo said:
Hi John I have created a table for the Channels and named it Channels.
I
have
created a new query and pasted your SQL but I get a syntax error? can
you
help plz..
SELECT Channels.Channel
FROM Channel
WHERE NOT EXISTS(
SELECT Channel FROM Enter Talyform Data
WHERE [Date:] BETWEEN DateAdd('d", -15, Date()) AND Date()


:

Hi Jerry, I shall try and make this short!!
Type is a Bearing designation. Channel is a production line. Date: is
the
date when entered.
Type Channel Date:
22210cc PC6 10/09/2009
22212cc Pc8 12/09/2009
22310cc pc4 21/09/2009
23218cc pc7 23/09/2009
The query I have at the moment will show the above of the last 15
days.
I want a query to show the last 15 days and also PC2 [Channel] was
not
entered. is this possible?

I presume you have a table of all valid Channel values? If not, then
you
could
be stuck saying that PC31 and PQ9341234AAcD54 were not entered...

Given such a table, an "Unmatched" query will work:

SELECT Channels.Channel
FROM Channel
WHERE NOT EXISTS(
SELECT Channel FROM yourtable
WHERE [Date] BETWEEN DateAdd('d", -15, Date()) AND Date()
Click to expand...
Click to expand...
Click to expand...
 
Hi Douglas I still get this error:
Missing),], or Item in query expression 'NOT EXISTS( SELECT Channel FROM
[Enter Talyform Data] WHERE [Dtae:] BETWEEN DateAdd("d",-15,Date()) AND Date()

Douglas J. Steele said:
Sorry: copy-and-paste error. You need double-quotes both before and after
the d, not a single quote in front and a double quote afterwards. It's what
tells the DateAdd function to subtract 15 days from the current date.

SELECT Channels.Channel
FROM Channel
WHERE NOT EXISTS(
SELECT Channel FROM [Enter Talyform Data]
WHERE [Date:] BETWEEN DateAdd("d", -15, Date()) AND Date()

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



jo said:
Hi Doug thanks for your help but I still get a syntax error.. I'm not sure
wot this is 'd"? My date has a semi-colon Date: but I have tried to add
that
but no joy??

Douglas J. Steele said:
If your table's named Enter Talyform Data, you need to use square
brackets
due to the space:

SELECT Channels.Channel
FROM Channel
WHERE NOT EXISTS(
SELECT Channel FROM [Enter Talyform Data]
WHERE [Date] BETWEEN DateAdd('d", -15, Date()) AND Date()

Note that I also removed the semicolon from the field name Date in the
Where
clause. As far as I know, field names cannot contain colons. If your
intent
is to prompt the user to enter a date, that subquery is pretty
meaningless.

Incidentally, you should consider renaming your field name. Date is a
reserved word, and you should never use reserved words for your own
purposes. For a comprehensive list of names to avoid (as well as a link
to a
free utility to check your application for compliance), see what Allen
Browne has at http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Hi John I have created a table for the Channels and named it Channels.
I
have
created a new query and pasted your SQL but I get a syntax error? can
you
help plz..
SELECT Channels.Channel
FROM Channel
WHERE NOT EXISTS(
SELECT Channel FROM Enter Talyform Data
WHERE [Date:] BETWEEN DateAdd('d", -15, Date()) AND Date()


:

Hi Jerry, I shall try and make this short!!
Type is a Bearing designation. Channel is a production line. Date: is
the
date when entered.
Type Channel Date:
22210cc PC6 10/09/2009
22212cc Pc8 12/09/2009
22310cc pc4 21/09/2009
23218cc pc7 23/09/2009
The query I have at the moment will show the above of the last 15
days.
I want a query to show the last 15 days and also PC2 [Channel] was
not
entered. is this possible?

I presume you have a table of all valid Channel values? If not, then
you
could
be stuck saying that PC31 and PQ9341234AAcD54 were not entered...

Given such a table, an "Unmatched" query will work:

SELECT Channels.Channel
FROM Channel
WHERE NOT EXISTS(
SELECT Channel FROM yourtable
WHERE [Date] BETWEEN DateAdd('d", -15, Date()) AND Date()
Click to expand...
Click to expand...
Click to expand...
 
jo said:
Hi Douglas I still get this error:
Missing),], or Item in query expression 'NOT EXISTS( SELECT Channel FROM
[Enter Talyform Data] WHERE [Dtae:] BETWEEN DateAdd("d",-15,Date()) AND Date()
Click to expand...

You need one more closing parenthesis to match the open parenthesis
after the EXISTS key word:

SELECT Channels.Channel
FROM Channel
WHERE NOT EXISTS (
SELECT Channel FROM [Enter Talyform Data]
WHERE [Date:] BETWEEN DateAdd("d", -15, Date()) AND Date())

However, that still doesn't look right to me. You can't "SELECT
Channels.Channel FROM Channel". The name which follows "FROM" should be
the name of a table (or query). Is your table named Channel or Channels?

In your latest example, you include "[Dtae:]" following WHERE. Is that
correct?
 
jo said:
Hi Douglas I still get this error:
Missing),], or Item in query expression 'NOT EXISTS( SELECT Channel FROM
[Enter Talyform Data] WHERE [Dtae:] BETWEEN DateAdd("d",-15,Date()) AND Date()
Click to expand...

You need one more closing parenthesis to match the open parenthesis
after the EXISTS key word:

SELECT Channels.Channel
FROM Channel
WHERE NOT EXISTS (
SELECT Channel FROM [Enter Talyform Data]
WHERE [Date:] BETWEEN DateAdd("d", -15, Date()) AND Date())

However, that still doesn't look right to me. You can't "SELECT
Channels.Channel FROM Channel". The name which follows "FROM" should be
the name of a table (or query). Is your table named Channel or Channels?

In your latest example, you include "[Dtae:]" following WHERE. Is that
correct?
 
Hi Hans [Dtae] was just a typo.. Not a cut & paste..
I dont get a syntax error now but it comes up with a input box with saying
Channels.Channel OK or Cancel button?
The user would put the Channel in the Table named [Enter Talyform Data] and
I have a table named [Channels] which has the all the channels names e.g
PC2,PC3,PC4 etc and the query is named [NoChecks]

Hans Up said:
jo said:
Hi Douglas I still get this error:
Missing),], or Item in query expression 'NOT EXISTS( SELECT Channel FROM
[Enter Talyform Data] WHERE [Dtae:] BETWEEN DateAdd("d",-15,Date()) AND Date()
Click to expand...

You need one more closing parenthesis to match the open parenthesis
after the EXISTS key word:

SELECT Channels.Channel
FROM Channel
WHERE NOT EXISTS (
SELECT Channel FROM [Enter Talyform Data]
WHERE [Date:] BETWEEN DateAdd("d", -15, Date()) AND Date())

However, that still doesn't look right to me. You can't "SELECT
Channels.Channel FROM Channel". The name which follows "FROM" should be
the name of a table (or query). Is your table named Channel or Channels?

In your latest example, you include "[Dtae:]" following WHERE. Is that
correct?
Click to expand...
 
jo said:
Hi Hans [Dtae] was just a typo.. Not a cut & paste..
Click to expand...

Just a note: Date is a reserved word by Access and may cause confusion
on Access's part. You don't want Access to think you were asking for the
today's date as invoked by Date function, rather than a column named
"Date". For this reasons, it's best to avoid using reserved words although.
 
Hi I have got the message about Names but I cant change mine at the mo cause
everything works so next time I shall watch wot I name things ok

Banana said:
jo said:
Hi Hans [Dtae] was just a typo.. Not a cut & paste..
Click to expand...

Just a note: Date is a reserved word by Access and may cause confusion
on Access's part. You don't want Access to think you were asking for the
today's date as invoked by Date function, rather than a column named
"Date". For this reasons, it's best to avoid using reserved words although.
Click to expand...
 
jo said:
Hi Hans [Dtae] was just a typo.. Not a cut & paste..
I dont get a syntax error now but it comes up with a input box with saying
Channels.Channel OK or Cancel button?
Click to expand...

Please show us the SQL. Copy and paste.
 
Back
Top