query / combo box

[ghostman via AccessMonster.com]

again, i have this query for my report:

SELECT qryNTSMonth.ID, qryNTSMonth.TraineeID, Trainees.LastName, Trainees.
FirstName, Trainees.JobTitle, Trainees.ContractorName, Trainees.
SupervisorContactNo, qryNTSMonth.TSID, qryNTSMonth.NTSMonth, qryNTSMonth.
NTSModule
FROM Trainees INNER JOIN qryNTSMonth ON Trainees.TraineeID = qryNTSMonth.
TraineeID
WHERE (((qryNTSMonth.NTSMonth) Like [Forms]![frmNTSMonthScheduleSelect]!
[cboSelectMonth] & "*"));

and a combo box [cboSelectMonth] which has a row source of:
1;"January";2;"February";......."December"
and a command button [macro] to open the report based on the combo box
selection.

The purpose is to display the next training session based on the month
selection from the combo box. So, if September is selected, it will display
schedule training session for September.

I got it all working except for the month of January. If January is selected,
all scheduled training sessions shows up while from Feb up to December are
working fine. Like, i dont have training schedule on February, and shows no
record (works fine!) and so as the others showing correct record. I cant
figure out why January show all records..

something is wrong..

 

[Allen Browne]

Use = instead of like:
WHERE qryNTSMonth.NTSMonth
=[Forms]![frmNTSMonthScheduleSelect]![cboSelectMonth];

Like performs a string operation. If you compare character by character,
than 10, 11, and 12 are all like 1*.

 

[ghostman via AccessMonster.com]

i tried this two:

1) WHERE (((qryNTSMonth.NTSMonth)=[Forms]![frmNTSMonthScheduleSelect]!
[cboSelectMonth]));

2) WHERE (((qryNTSMonth.NTSMonth)=[Forms]![frmNTSMonthScheduleSelect]!
[cboSelectMonth] & "*"));

but I got an error message "This expression is type incorrectly, or its too
complex to be evaluated...."

???

Use = instead of like:
WHERE qryNTSMonth.NTSMonth
=[Forms]![frmNTSMonthScheduleSelect]![cboSelectMonth];

Like performs a string operation. If you compare character by character,
than 10, 11, and 12 are all like 1*.

again, i have this query for my report:

[quoted text clipped - 26 lines]

something is wrong..

 

[John W. Vinson]

i tried this two:

1) WHERE (((qryNTSMonth.NTSMonth)=[Forms]![frmNTSMonthScheduleSelect]!
[cboSelectMonth]));

2) WHERE (((qryNTSMonth.NTSMonth)=[Forms]![frmNTSMonthScheduleSelect]!
[cboSelectMonth] & "*"));

but I got an error message "This expression is type incorrectly, or its too
complex to be evaluated...."

Well, the second one can't possibly return any data unless the field NTSMonth
is a Text field which actually ends with a literal asterisk character.

The first one MIGHT work - depending on the datatype of NTSMonth (which I
can't see) and the actual value returned by cboSelectMonth (which I can't see
either).

what's in the table? What's in the combo?

 

[ghostman via AccessMonster.com]

COMBO BOX:
Combo Box Name: cboSelectMonth
Control Source: [empty]
Row Source: 1;"January";2;"February";3;......12;"December"
Row Source Type: Value List
Bound Column: 1
Column Count: 2
Column Width: 0"

NTSMonth is a Date Field

The combo box selection opens a report based on a query. [qryNTSMonth]

and i use this criteria under NTSMonth field:
[Forms]![frmNTSMonthScheduleSelect]![cboSelectMonth]

and i get an error....

i tried this two:

[quoted text clipped - 6 lines]

but I got an error message "This expression is type incorrectly, or its too
complex to be evaluated...."

Well, the second one can't possibly return any data unless the field NTSMonth
is a Text field which actually ends with a literal asterisk character.

The first one MIGHT work - depending on the datatype of NTSMonth (which I
can't see) and the actual value returned by cboSelectMonth (which I can't see
either).

what's in the table? What's in the combo?

 

[Allen Browne]

The "Too complex" error message often indicates that you have the wrong type
of data matching going on here.

What is the data type of the NTSMonth field when you open your table in
design view? Or is it a calculated expression in a query?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

COMBO BOX:
Combo Box Name: cboSelectMonth
Control Source: [empty]
Row Source: 1;"January";2;"February";3;......12;"December"
Row Source Type: Value List
Bound Column: 1
Column Count: 2
Column Width: 0"

NTSMonth is a Date Field

The combo box selection opens a report based on a query. [qryNTSMonth]

and i use this criteria under NTSMonth field:
[Forms]![frmNTSMonthScheduleSelect]![cboSelectMonth]

and i get an error....

i tried this two:

[quoted text clipped - 6 lines]

but I got an error message "This expression is type incorrectly, or its
too
complex to be evaluated...."

Well, the second one can't possibly return any data unless the field
NTSMonth
is a Text field which actually ends with a literal asterisk character.

The first one MIGHT work - depending on the datatype of NTSMonth (which I
can't see) and the actual value returned by cboSelectMonth (which I can't
see
either).

what's in the table? What's in the combo?

 

[John W. Vinson]

COMBO BOX:
Combo Box Name: cboSelectMonth
Control Source: [empty]
Row Source: 1;"January";2;"February";3;......12;"December"
Row Source Type: Value List
Bound Column: 1
Column Count: 2
Column Width: 0"

I think you want ColumnWidths set to something like

0";0.75"

to conceal the month number but display the textstring month name.

NTSMonth is a Date Field

And there's your problem. You're trying to store an integer digit - 1 to 12 -
into a Date/Time value.

The combo box selection opens a report based on a query. [qryNTSMonth]

and i use this criteria under NTSMonth field:
[Forms]![frmNTSMonthScheduleSelect]![cboSelectMonth]

If the user selects September, what records do you want to see? September this
year? the most recently passed September? September in any year in the table?
A criterion of

9

on a Date/TIme field will (if it works at all) return all records on January
7, 1900... probably not what you want!

For the month in the current year, try a criterion of

= DateSerial(Year(Date()), [Forms]![frmNTSMonthScheduleSelect]![cboSelectMonth], 1) AND < DateSerial(Year(Date()), [Forms]![frmNTSMonthScheduleSelect]![cboSelectMonth] + 1, 1)

 

[ghostman via AccessMonster.com]

NTSMonth field is a Date/Time field.

and in the query under NTSMonth, i add this criteria:
[Forms]![frmNTSMonthScheduleSelect]![cboSelectMonth]

The "Too complex" error message often indicates that you have the wrong type
of data matching going on here.

What is the data type of the NTSMonth field when you open your table in
design view? Or is it a calculated expression in a query?

COMBO BOX:
Combo Box Name: cboSelectMonth

[quoted text clipped - 31 lines]

 

[ghostman via AccessMonster.com]

thanks again!

now its working great and it displays only the training sessions schedule for
this year...that's what i need.

sorry if im giving you guys trouble but how can i make it NOT to display
the previous months [Jan - Aug] of the current year and display only
September to December 2009.

COMBO BOX:
Combo Box Name: cboSelectMonth

[quoted text clipped - 4 lines]

Column Count: 2
Column Width: 0"

I think you want ColumnWidths set to something like

0";0.75"

to conceal the month number but display the textstring month name.

NTSMonth is a Date Field

And there's your problem. You're trying to store an integer digit - 1 to 12 -
into a Date/Time value.

The combo box selection opens a report based on a query. [qryNTSMonth]

and i use this criteria under NTSMonth field:
[Forms]![frmNTSMonthScheduleSelect]![cboSelectMonth]

If the user selects September, what records do you want to see? September this
year? the most recently passed September? September in any year in the table?
A criterion of

9

on a Date/TIme field will (if it works at all) return all records on January
7, 1900... probably not what you want!

For the month in the current year, try a criterion of

= DateSerial(Year(Date()), [Forms]![frmNTSMonthScheduleSelect]![cboSelectMonth], 1) AND < DateSerial(Year(Date()), [Forms]![frmNTSMonthScheduleSelect]![cboSelectMonth] + 1, 1)

 

[Allen Browne]

Okay: there's the problem.

You are matching a date/time value against a number between 1 and 12. It's
not going to match.

 

[John W. Vinson]

thanks again!

now its working great and it displays only the training sessions schedule for
this year...that's what i need.

sorry if im giving you guys trouble but how can i make it NOT to display
the previous months [Jan - Aug] of the current year and display only
September to December 2009.

When you select September from the combo box, you mean? If you select January
you want to see the whole year, September you want to see Sept-Dec, December
only December?

= DateSerial(Year(Date()), [Forms]![frmNTSMonthScheduleSelect]![cboSelectMonth], 1) AND < DateSerial(Year(Date() + 1, 1)

 

[ghostman via AccessMonster.com]

We are in the month of September 2009, so it will only show training
schedules from September 2009 to December 2009..Training schedules for
January 2009 until August 2009 are already completed and no need to show in
the records.

thanks again!

[quoted text clipped - 4 lines]

the previous months [Jan - Aug] of the current year and display only
September to December 2009.

When you select September from the combo box, you mean? If you select January
you want to see the whole year, September you want to see Sept-Dec, December
only December?

= DateSerial(Year(Date()), [Forms]![frmNTSMonthScheduleSelect]![cboSelectMonth], 1) AND < DateSerial(Year(Date() + 1, 1)

 

[John W. Vinson]

We are in the month of September 2009, so it will only show training
schedules from September 2009 to December 2009..Training schedules for
January 2009 until August 2009 are already completed and no need to show in
the records.

Then my suggested query should work. Did you try it?

 

[ghostman via AccessMonster.com]

the query works great but only the problem is it shows the month that has a
completed training schedule. (shows training schedule from May 2009), like i
said, if we are in September, it should not display any record from previous
months and display only for the current month up to december 2009.

SELECT [Training Records].ID, [Training Records].TraineeID, [Training Records]
.TSID, [Training Records].NTSMonth, [Training Records].NTSModule, [Training
Records].NTSMonth
FROM Trainees INNER JOIN [Training Records] ON Trainees.TraineeID = [Training
Records].TraineeID
WHERE ((([Training Records].NTSMonth)>=DateSerial(Year(Date()),[Forms]!
[frmNTSMonthScheduleSelect]![cboSelectMonth],1) And ([Training Records].
NTSMonth)<DateSerial(Year(Date()),[Forms]![frmNTSMonthScheduleSelect]!
[cboSelectMonth]+1,1)));

 

[John W. Vinson]

the query works great but only the problem is it shows the month that has a
completed training schedule. (shows training schedule from May 2009), like i
said, if we are in September, it should not display any record from previous
months and display only for the current month up to december 2009.

SELECT [Training Records].ID, [Training Records].TraineeID, [Training Records]
TSID, [Training Records].NTSMonth, [Training Records].NTSModule, [Training
Records].NTSMonth
FROM Trainees INNER JOIN [Training Records] ON Trainees.TraineeID = [Training
Records].TraineeID
WHERE ((([Training Records].NTSMonth)>=DateSerial(Year(Date()),[Forms]!
[frmNTSMonthScheduleSelect]![cboSelectMonth],1) And ([Training Records].
NTSMonth)<DateSerial(Year(Date()),[Forms]![frmNTSMonthScheduleSelect]!
[cboSelectMonth]+1,1)));

Then do what I suggested, instead of leaving the end of the criterion the way
you had it:

SELECT [Training Records].ID, [Training Records].TraineeID, [Training Records]
TSID, [Training Records].NTSMonth, [Training Records].NTSModule, [Training
Records].NTSMonth
FROM Trainees INNER JOIN [Training Records] ON Trainees.TraineeID = [Training
Records].TraineeID
WHERE ((([Training Records].NTSMonth)>=DateSerial(Year(Date()),[Forms]!
[frmNTSMonthScheduleSelect]![cboSelectMonth],1) And ([Training Records].
NTSMonth)<DateSerial(Year(Date())+1,1,1)));