too complex query

  • Thread starter Thread starter Barb
  • Start date Start date
B

Barb

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]));
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Thank you Dale
It worked perfectly

Barb

Dale Fye said:
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 said:
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 said:
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


:

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
Click to expand...
Click to expand...


.
Click to expand...
 
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/AppIssueBadWord.html
 
Back
Top