Combo Box Query Not Working

G

Guest

I’m new to access but learning quickly. I’m trying to create a data base for
our “time change†items on out plant equipment. Such as;

Furnace (asset)

Clutch (part)

Installed date (date installed)

Replacement date (set date in the future e.g. 6 months from install date)

I already have a table with the columns named asset, part, installed date
and replacement date. It’s called Table 1.

All dates are formatted to medium date format and input mask the same.
I also have a table with IntervalTitle, Interval and IntervalIncrement set
as follows:

IntervalTitle Interval
IntervalIncrement
1 week d 7
2 weeks d 14
1 month m 1
3 month m 3
6 month m 6
12 month m 12

IntervalTitle and Interval are TEXT and IntervalIncrement in Date/Time, Date
medium, input mask the same. It’s called tblDateIntervals

I have a query (called DateIntervals) to find the wanted date that the
interval is asking for. The SQL is as follows:
SELECT tblDateIntervals.IntervalTitle,
DateAdd([Interval],[IntervalIncrement],Date()) AS CalcDate
FROM tblDateIntervals
ORDER BY DateAdd([Interval],[IntervalIncrement],Date());

This works! The query shows two columns. One is called IntervalTitle and it
shows the interval asked (1 week, 2 weeks etc…). The other is the CalcDate
that shows the correct calculated time and expresses it as medium date.

Finally to my dilemma!

I have created a form to enter the data to the above fields in the table,
they all work. Then I created a combo box to enter the calculated date to the
original first table (Table 1, Replacement Date). The combo box properties
are set as follows:
Control source Replacement Date (from Table 1)
Format dd-mmm-yy
Row Source Type Table/Query
Row Source DateIntervals (from the query)

Now when I try to use the form, everything works up until I try to use the
combo box to select the proper time interval (1 week, 2 weeks etc…). The time
intervals show up in the combo box when selected, however when selected, I
get an error message “The Value You Entered Isn’t Valid For This Fieldâ€
Arrrrrr……So close!!!

Any suggestions?????? Thanks in advanced
 
G

Guest

The value you are trying to write to the table should be the value that you
have defined in the table. In your case you've defined a date/time field. So
what you select from the combo should be a datetime value. Did you define two
columns for the combo? In that case change the bound column to the column
where the date is placed e.g. if the date field is the second column in your
column the bound column should be set to 2.

hth
--
Maurice Ausum


Jeff G said:
I’m new to access but learning quickly. I’m trying to create a data base for
our “time change†items on out plant equipment. Such as;

Furnace (asset)

Clutch (part)

Installed date (date installed)

Replacement date (set date in the future e.g. 6 months from install date)

I already have a table with the columns named asset, part, installed date
and replacement date. It’s called Table 1.

All dates are formatted to medium date format and input mask the same.
I also have a table with IntervalTitle, Interval and IntervalIncrement set
as follows:

IntervalTitle Interval
IntervalIncrement
1 week d 7
2 weeks d 14
1 month m 1
3 month m 3
6 month m 6
12 month m 12

IntervalTitle and Interval are TEXT and IntervalIncrement in Date/Time, Date
medium, input mask the same. It’s called tblDateIntervals

I have a query (called DateIntervals) to find the wanted date that the
interval is asking for. The SQL is as follows:
SELECT tblDateIntervals.IntervalTitle,
DateAdd([Interval],[IntervalIncrement],Date()) AS CalcDate
FROM tblDateIntervals
ORDER BY DateAdd([Interval],[IntervalIncrement],Date());

This works! The query shows two columns. One is called IntervalTitle and it
shows the interval asked (1 week, 2 weeks etc…). The other is the CalcDate
that shows the correct calculated time and expresses it as medium date.

Finally to my dilemma!

I have created a form to enter the data to the above fields in the table,
they all work. Then I created a combo box to enter the calculated date to the
original first table (Table 1, Replacement Date). The combo box properties
are set as follows:
Control source Replacement Date (from Table 1)
Format dd-mmm-yy
Row Source Type Table/Query
Row Source DateIntervals (from the query)

Now when I try to use the form, everything works up until I try to use the
combo box to select the proper time interval (1 week, 2 weeks etc…). The time
intervals show up in the combo box when selected, however when selected, I
get an error message “The Value You Entered Isn’t Valid For This Fieldâ€
Arrrrrr……So close!!!

Any suggestions?????? Thanks in advanced
Click to expand...
 
G

Guest

Works perfectly!!!! Thanks
--
Jeff G
Maintenance Tech
Milgard Tempering


Maurice said:
The value you are trying to write to the table should be the value that you
have defined in the table. In your case you've defined a date/time field. So
what you select from the combo should be a datetime value. Did you define two
columns for the combo? In that case change the bound column to the column
where the date is placed e.g. if the date field is the second column in your
column the bound column should be set to 2.

hth
--
Maurice Ausum


Jeff G said:
I’m new to access but learning quickly. I’m trying to create a data base for
our “time change†items on out plant equipment. Such as;

Furnace (asset)

Clutch (part)

Installed date (date installed)

Replacement date (set date in the future e.g. 6 months from install date)

I already have a table with the columns named asset, part, installed date
and replacement date. It’s called Table 1.

All dates are formatted to medium date format and input mask the same.
I also have a table with IntervalTitle, Interval and IntervalIncrement set
as follows:

IntervalTitle Interval
IntervalIncrement
1 week d 7
2 weeks d 14
1 month m 1
3 month m 3
6 month m 6
12 month m 12

IntervalTitle and Interval are TEXT and IntervalIncrement in Date/Time, Date
medium, input mask the same. It’s called tblDateIntervals

I have a query (called DateIntervals) to find the wanted date that the
interval is asking for. The SQL is as follows:
SELECT tblDateIntervals.IntervalTitle,
DateAdd([Interval],[IntervalIncrement],Date()) AS CalcDate
FROM tblDateIntervals
ORDER BY DateAdd([Interval],[IntervalIncrement],Date());

This works! The query shows two columns. One is called IntervalTitle and it
shows the interval asked (1 week, 2 weeks etc…). The other is the CalcDate
that shows the correct calculated time and expresses it as medium date.

Finally to my dilemma!

I have created a form to enter the data to the above fields in the table,
they all work. Then I created a combo box to enter the calculated date to the
original first table (Table 1, Replacement Date). The combo box properties
are set as follows:
Control source Replacement Date (from Table 1)
Format dd-mmm-yy
Row Source Type Table/Query
Row Source DateIntervals (from the query)

Now when I try to use the form, everything works up until I try to use the
combo box to select the proper time interval (1 week, 2 weeks etc…). The time
intervals show up in the combo box when selected, however when selected, I
get an error message “The Value You Entered Isn’t Valid For This Fieldâ€
Arrrrrr……So close!!!

Any suggestions?????? Thanks in advanced
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combo Box Not Working 2
Connecting Text Box in Query Criteria 4
Append query and combo box on the form 1
Image Based on Combo Box Selection 3
Diff combo box for same field 6
Getting error: ‘Object or class does not support the set of events 2
Report displays numbers, not appropriate categories 10
Cascading Combo Boxes and Select "ALL" 2

Top