too complex query

hello
I'm using Access 2003 SP3 and here is my query based on single table

PARAMETERS StartDate DateTime, EndDate DateTime;
SELECT tblCalendarHistory.CalendarID, tblCalendarHistory.Date,
tblCalendarHistory.Division
FROM tblCalendarHistory
WHERE (((tblCalendarHistory.Date) Between "StartDate" And "EndDate") AND
((tblCalendarHistory.Division)=[Forms]![frmDivision]![txtDivision]));

At first I created a form for imputting start and End dates, but I was
getting "Too complex" message. So I created parameters - same message. If I
replace parameters with actual dates - then it works. I can't figure out what
can be too complex in this thing..

please help, I'd trully appreciate any ideas

Barb

IMO parameter prompts are not good user interface. I think controls on forms
offer much more functionality.

Parameters must have [] not quotes. You might also want to add the
txtDivision to the parameters.

Try:
PARAMETERS [StartDate] DateTime, [EndDate] DateTime;
SELECT tblCalendarHistory.CalendarID, tblCalendarHistory.Date,
tblCalendarHistory.Division
FROM tblCalendarHistory
WHERE (((tblCalendarHistory.Date) Between [StartDate] And [EndDate') AND
((tblCalendarHistory.Division)=[Forms]![frmDivision]![txtDivision]));

--
Duane Hookom
Microsoft Access MVP


"Barb" wrote:

hello
I'm using Access 2003 SP3 and here is my query based on single table

PARAMETERS StartDate DateTime, EndDate DateTime;
SELECT tblCalendarHistory.CalendarID, tblCalendarHistory.Date,
tblCalendarHistory.Division
FROM tblCalendarHistory
WHERE (((tblCalendarHistory.Date) Between "StartDate" And "EndDate") AND
((tblCalendarHistory.Division)=[Forms]![frmDivision]![txtDivision]));

At first I created a form for imputting start and End dates, but I was
getting "Too complex" message. So I created parameters - same message. If I
replace parameters with actual dates - then it works. I can't figure out what
can be too complex in this thing..

please help, I'd trully appreciate any ideas

Barb

Thank you Duane
You trully are a master.
[] worked - it's always the simple things that get you.

Could you help me more?
I agree it should be based on imput form, that was my original thought.
so, here is the same query tied to the form, again I'm getting "Too
complex'message

SELECT tblCalendarHistory.CalendarID, tblCalendarHistory.Date,
tblCalendarHistory.Division
FROM tblCalendarHistory
WHERE (((tblCalendarHistory.Date) Between
[Forms]![frmLocationsBookedNotServicedEntry]![txtStartDate] And
[Forms]![frmLocationsBookedNotServicedEntry]![txtEndDate]) AND
((tblCalendarHistory.Division)=[Forms]![frmDivision]![txtDivision]));

please and thank you

Barb

"Duane Hookom" wrote:

IMO parameter prompts are not good user interface. I think controls on forms
offer much more functionality.

Parameters must have [] not quotes. You might also want to add the
txtDivision to the parameters.

Try:
PARAMETERS [StartDate] DateTime, [EndDate] DateTime;
SELECT tblCalendarHistory.CalendarID, tblCalendarHistory.Date,
tblCalendarHistory.Division
FROM tblCalendarHistory
WHERE (((tblCalendarHistory.Date) Between [StartDate] And [EndDate') AND
((tblCalendarHistory.Division)=[Forms]![frmDivision]![txtDivision]));

--
Duane Hookom
Microsoft Access MVP


"Barb" wrote:

hello
I'm using Access 2003 SP3 and here is my query based on single table

PARAMETERS StartDate DateTime, EndDate DateTime;
SELECT tblCalendarHistory.CalendarID, tblCalendarHistory.Date,
tblCalendarHistory.Division
FROM tblCalendarHistory
WHERE (((tblCalendarHistory.Date) Between "StartDate" And "EndDate") AND
((tblCalendarHistory.Division)=[Forms]![frmDivision]![txtDivision]));

At first I created a form for imputting start and End dates, but I was
getting "Too complex" message. So I created parameters - same message. If I
replace parameters with actual dates - then it works. I can't figure out what
can be too complex in this thing..

please help, I'd trully appreciate any ideas

Barb

Barb,

You may still have to either declare the parameters, or explicitly type them
in the query. Try:

SELECT tblCalendarHistory.CalendarID,
tblCalendarHistory.Date,
tblCalendarHistory.Division
FROM tblCalendarHistory
WHERE tblCalendarHistory.Date
BETWEEN cDate([Forms]![frmLocationsBookedNotServicedEntry]![txtStartDate])
AND cDate([Forms]![frmLocationsBookedNotServicedEntry]![txtEndDate])
AND tblCalendarHistory.Division=[Forms]![frmDivision]![txtDivision]

HTH
Dale


"Barb" wrote in message
...
Thank you Duane
You trully are a master.
[] worked - it's always the simple things that get you.

Could you help me more?
I agree it should be based on imput form, that was my original thought.
so, here is the same query tied to the form, again I'm getting "Too
complex'message

SELECT tblCalendarHistory.CalendarID, tblCalendarHistory.Date,
tblCalendarHistory.Division
FROM tblCalendarHistory
WHERE (((tblCalendarHistory.Date) Between
[Forms]![frmLocationsBookedNotServicedEntry]![txtStartDate] And
[Forms]![frmLocationsBookedNotServicedEntry]![txtEndDate]) AND
((tblCalendarHistory.Division)=[Forms]![frmDivision]![txtDivision]));

please and thank you

Barb

"Duane Hookom" wrote:

IMO parameter prompts are not good user interface. I think controls on
forms
offer much more functionality.

Parameters must have [] not quotes. You might also want to add the
txtDivision to the parameters.

Try:
PARAMETERS [StartDate] DateTime, [EndDate] DateTime;
SELECT tblCalendarHistory.CalendarID, tblCalendarHistory.Date,
tblCalendarHistory.Division
FROM tblCalendarHistory
WHERE (((tblCalendarHistory.Date) Between [StartDate] And [EndDate') AND
((tblCalendarHistory.Division)=[Forms]![frmDivision]![txtDivision]));

--
Duane Hookom
Microsoft Access MVP


"Barb" wrote:

hello
I'm using Access 2003 SP3 and here is my query based on single table

PARAMETERS StartDate DateTime, EndDate DateTime;
SELECT tblCalendarHistory.CalendarID, tblCalendarHistory.Date,
tblCalendarHistory.Division
FROM tblCalendarHistory
WHERE (((tblCalendarHistory.Date) Between "StartDate" And "EndDate")
AND
((tblCalendarHistory.Division)=[Forms]![frmDivision]![txtDivision]));

At first I created a form for imputting start and End dates, but I was
getting "Too complex" message. So I created parameters - same message.
If I
replace parameters with actual dates - then it works. I can't figure
out what
can be too complex in this thing..

please help, I'd trully appreciate any ideas

Barb

Thank you Dale
It worked perfectly

Barb

"Dale Fye" wrote:

Barb,

You may still have to either declare the parameters, or explicitly type them
in the query. Try:

SELECT tblCalendarHistory.CalendarID,
tblCalendarHistory.Date,
tblCalendarHistory.Division
FROM tblCalendarHistory
WHERE tblCalendarHistory.Date
BETWEEN cDate([Forms]![frmLocationsBookedNotServicedEntry]![txtStartDate])
AND cDate([Forms]![frmLocationsBookedNotServicedEntry]![txtEndDate])
AND tblCalendarHistory.Division=[Forms]![frmDivision]![txtDivision]

HTH
Dale


"Barb" wrote in message
...
Thank you Duane
You trully are a master.
[] worked - it's always the simple things that get you.

Could you help me more?
I agree it should be based on imput form, that was my original thought.
so, here is the same query tied to the form, again I'm getting "Too
complex'message

SELECT tblCalendarHistory.CalendarID, tblCalendarHistory.Date,
tblCalendarHistory.Division
FROM tblCalendarHistory
WHERE (((tblCalendarHistory.Date) Between
[Forms]![frmLocationsBookedNotServicedEntry]![txtStartDate] And
[Forms]![frmLocationsBookedNotServicedEntry]![txtEndDate]) AND
((tblCalendarHistory.Division)=[Forms]![frmDivision]![txtDivision]));

please and thank you

Barb

"Duane Hookom" wrote:

IMO parameter prompts are not good user interface. I think controls on
forms
offer much more functionality.

Parameters must have [] not quotes. You might also want to add the
txtDivision to the parameters.

Try:
PARAMETERS [StartDate] DateTime, [EndDate] DateTime;
SELECT tblCalendarHistory.CalendarID, tblCalendarHistory.Date,
tblCalendarHistory.Division
FROM tblCalendarHistory
WHERE (((tblCalendarHistory.Date) Between [StartDate] And [EndDate') AND
((tblCalendarHistory.Division)=[Forms]![frmDivision]![txtDivision]));

--
Duane Hookom
Microsoft Access MVP


"Barb" wrote:

hello
I'm using Access 2003 SP3 and here is my query based on single table

PARAMETERS StartDate DateTime, EndDate DateTime;
SELECT tblCalendarHistory.CalendarID, tblCalendarHistory.Date,
tblCalendarHistory.Division
FROM tblCalendarHistory
WHERE (((tblCalendarHistory.Date) Between "StartDate" And "EndDate")
AND
((tblCalendarHistory.Division)=[Forms]![frmDivision]![txtDivision]));

At first I created a form for imputting start and End dates, but I was
getting "Too complex" message. So I created parameters - same message.
If I
replace parameters with actual dates - then it works. I can't figure
out what
can be too complex in this thing..

please help, I'd trully appreciate any ideas

Barb


.

The problem is twofold:

a) You used quotes instead of square brackets around your parameter names,
so Access is trying to compare a date to the literal text "StartDate" which
doesn't compute.

b) You have a reserved word (Date) as a field name. You can try adding
square brackets around the bad name, but a better solution would be to
rename the field.

Try:
WHERE tblCalendarHistory.[Date] Between [StartDate] And [EndDate]

For a list of names to avoid when adding fields to your tables, see:
http://allenbrowne.com/Ap****ueBadWord.html

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


"Barb" wrote in message
...
hello
I'm using Access 2003 SP3 and here is my query based on single table

PARAMETERS StartDate DateTime, EndDate DateTime;
SELECT tblCalendarHistory.CalendarID, tblCalendarHistory.Date,
tblCalendarHistory.Division
FROM tblCalendarHistory
WHERE (((tblCalendarHistory.Date) Between "StartDate" And "EndDate") AND
((tblCalendarHistory.Division)=[Forms]![frmDivision]![txtDivision]));

At first I created a form for imputting start and End dates, but I was
getting "Too complex" message. So I created parameters - same message.
If I replace parameters with actual dates - then it works. I can't figure
out
what can be too complex in this thing..