Combo Box Query Not Working

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

-- 
Jeff G
Maintenance Tech
Milgard Tempering
0
Utf
9/13/2007 5:28:03 PM
access 16762 articles. 2 followers. Follow

2 Replies
741 Views

Similar Articles

[PageSpeed] 47

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" wrote:

> 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
> 
> -- 
> Jeff G
> Maintenance Tech
> Milgard Tempering
0
Utf
9/13/2007 7:18:05 PM
Works perfectly!!!!   Thanks
-- 
Jeff G
Maintenance Tech
Milgard Tempering


"Maurice" wrote:

> 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" wrote:
> 
> > 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
> > 
> > -- 
> > Jeff G
> > Maintenance Tech
> > Milgard Tempering
0
Utf
9/14/2007 8:16:02 PM
Reply:

Similar Artilces:

List box
I am trying to use the list box function from the control toolbar. I am not sure how to get the listings I want in the list box. Is there certain VB coding that is needed? Any help will be appreciated. Thanks. Todd No VBA code needed. In Design mode, click on your list box and select Properties from the Control toolbox (or right-click your list box and select Properties), then scroll down to the ListFillRange Property and indicate the cell range address that has the items you want to appear in the box (i.e., A1:A10). Then exit out of design mode and test your list box. MRO "Tod...

Automatically set the data type as date for a Make Table Query
I have a make table query that I run daily. One of the fields of the query is a date field. The source table for the query treats the information as a text string in a date format that is not recognized by access. I have created formula that parses the string to put it into standard "mm/dd/yy" format. However, when I run the make table query it treats the date field as text. This is a problem because I use a form to hold the criteria for a query that I run on the new table. In order to get the query to work, I have to manually set the data type to short date. This will ...

Database SQL query
Sorry for this post here. If someone knows a group I can go to for this question, please let me know. I have a program in MFC that accesses and updates an Access database. The problem is I can't figure out how to add a field which contains a word with a single apostrophe ie (St. Vincent's). I'm using the CDaoDatabase and CDaoRecordset classes. I understand that DAO is interpreting the apostrophe as some type of terminator but there must be a way around the issue. MS Access has information on how to do it in Access VB but that doesn't seem to work in MFC. Thanks Ughh ...

how do i view different mail boxes
I have set up different email accounts in outlook 2000. How do I choose which mail box I view when I go to outlook? btoonhoule <btoonhoule@discussions.microsoft.com> wrote: > I have set up different email accounts in outlook 2000. How do I > choose which mail box I view when I go to outlook? Using IMO or C/W mode? -- Brian Tillman ...

Counting in Query
Good morning, I am attempting to use the following in a query: Score HousCount: Val(IIf(IsNumeric([SPM Housekeeping].[Score A]),Count([SPM Housekeeping].[Score A]),0)+IIf(IsNumeric([SPM Housekeeping].[Score B]),Count([SPM Housekeeping].[Score B]),0)+IIf(IsNumeric([SPM Housekeeping].[Score C]),Count([SPM Housekeeping].[Score C]),0)+IIf(IsNumeric([SPM Housekeeping].[Score D]),Count([SPM Housekeeping].[Score D]),0)+IIf(IsNumeric([SPM Housekeeping].[Score E]),Count([SPM Housekeeping].[Score E]),0)+IIf(IsNumeric([SPM Housekeeping].[Score F]),Count([SPM Housekeeping].[Score F]),0)) When i ...

Concatenate Multi-Select List Box Items
I need to use the chosen items in a multi-select List Box as concatenated text in another control on a subform. This text will be part of a large amount of concatenated text. How do I do this? I did find info here about using such items in a query but it was way over my head and I couldn't figure out how to convert that idea to this issue so please don't just refer me to that w/o some other explanation for this novice. For example, if the user selects: Frt Bumper, Grille, & Headlamp in the List Box, I want to then somehow concatenate those selections into a sente...

Produce report from two queries from one table?
I am currently working upon a database which is being used to produce pricing qutoes. I have ran two queries to show item of products which have been quoted for and one which shows a sum of the remaing unquoted products. Each product is placed into different sections which relates to its main function, be it electrial or machineary. I am hoping to have a report which will allow me to show all quoted products under the sections, but have the unquoted products for the sections as one line entry in the report. I have ran a normal report showing full list details of both quoted and un-quo...

Filtering a combo box using a combo box
All, I am trying to filter the records from a table displayed in a combo box using a combo box on the same form. I built a query and set the criteria as Me![FormName]![Combo_box]. This allowed me to filter the list, but it did not do exactly what I wanted. When the first combo box is blank, the second one is also blank. Is there anyway I can get the second combo box to display all of the records when the first box is blank? Thank you for your help. Regards, John Try a criteria in the second combo of: Like Forms!YourFormName!Combo1Name & "*" -- Arvin Meyer, MCP, MVP http:/...

colour change of dated box
How can I change the colour of a dated box of excel after the date is over. I want the programme to read date automatically from pc and change colour. try Conditional Formatting In 2003: 1. Select the cells you want to format 2. Choose Format, Conditional Formatting 3. Choose Cell Value Is from the first drop down 3. Choose Formula is from the first drop down 4. Choose less than 5. In the third box, enter =TODAY() 6. Click the Format button 7. Choose a color on the Patterns tab (or any available option) 7. Click OK twice. -- Hope this is helpful Appreciate that you provide your feedbac...

excel 2003 not working when published to remote site
Using Excel 2003. Create a chart and have saved it as a web page and published to a remote share. When I save it locally, it works fine and is interactive,etc.. When I save it remotely and open the htm file in a web browser, it opens like the charting area but the data is not present. Gives error below in the pivot table that appears. Getting error The query could not be processed: o Error opening data file "file://\\remoteserver01\hassan\numbers_files\Numbers_Jan24_12558_cachedata001.xml". Any idea how to fix it ? I want others to access it remotely. Thanks ...

Combo Box on Forms
Hi, I am using a combo box on a form that has several items w/ one being Other. If the user selects other I want them to be able to input what they want so that it will go into the table instead of just the "Other". I just am not really sure on how to do that. Can you help? Thanks, Jaime In design view click on VIEW - Properties and the the combo. Then change Limit To List to No. -- KARL DEWEY Build a little - Test a little "jseger22@yahoo.com" wrote: > Hi, > > I am using a combo box on a form that has several items w/ one being > Other. If the use...

Eject (button and command) fails on both optical drives (CD & DVD) under XP. Works fine under Linux and pre XP starting
Both my drives (G: & H:) are fine and work perfectly once disks are loaded but I can no longer change disks while operating XP. I have to re-start XP and change the disks before XP starts.. They work perfectly under Linux (Mint8) although that is no longer installed and I've recently tested using the LIVE CD I've today disabled all start-up programs, re-booted and killed some processes that might be causing a problem but to no avail.. This has been happening for at least six months so I suspect some update at some has caused the problem.. The machine is fully up ...

Outlook synchronization/export to PDA from work laptop
I have Outlook 2000 on company laptop and we are prevented from loading software on the laptop, including synchronization software for pda use. Is there a workaround that I could export my Outlook 2000 from my laptop to a PDA? sirwilliam wrote: > I have Outlook 2000 on company laptop and we are prevented from > loading software on the laptop, including synchronization software > for pda use. Good thing, too - not re sync software, but you shouldn't be able to install or change anything on your work laptop, for many reasons. > Is there a workaround that I could export my...

sent box
how do i get the emails in the sent box to show sent to instead of sent from? -- deb deb wrote: > how do i get the emails in the sent box to show sent to instead of sent from? Add/remove whatever columns you want. Right-click on the header row and select Field Chooser. i may have not explained myself too well i dont want to sort by the "to" column i want the header on the email to show "to" not "from" i think the problem might be that this is not the default sent file, its just a folder named "sent" its for my boss, he ha...

Combo Boxes
Someone sent me an Excel document where each of the cells in I1:I312 are combo boxes. When you click off of those cells, the down arrow disappears. The list for the combo boxes are cells AA1 and AB1 (AA1 is New and AB1 is Existing). Please explain how this was done? Also, can you please tell me if it is possible to make New and Existing autocorrect entries. So even though the choices are New and Existing, if you hit N, New appears, and if you hit E, Existing appears. Thanks!! It sounds like Data|Validation. Take a look at Debra Dalgleish's site: http://www.contextures.com/xlDataVa...

Separator in combo box
hi, Is it possible to have a line separator in a combo box? I mean something like a separator in menus? thanks, Behzad Try :- http://www.codeproject.com/combobox/zsepcmb.asp http://www.codeproject.com/combobox/customcombo.asp -- Regards, Nish [VC++ MVP] http://www.voidnish.com /* MVP tips tricks and essays web site */ http://blog.voidnish.com /* My blog on C++/CLI, MFC, Whidbey, CLR... */ "behzad" <b@b.com> wrote in message news:%23fVB3em5EHA.3368@TK2MSFTNGP10.phx.gbl... > hi, > Is it possible to have a line separator in a combo box? I mean something > like...

Query Help 03-14-08
Hi, I have a table with two field: LOGNumber and VINumber I would like to see all the records where the VINumber is duplicated for a particular LOGNumber. Can anyone help me please. Thanks in advance. Try something like: SELECT LOGNumber, VINumber, Count(*) FROM YourTableName GROUP BY LOGNumber, VINumber HAVING Count(*) > 1 To build this query through the graphical query builder, create a new query and add your table to it. Drag to two fields into the grid, then type the LOGNumber field into the grid a second time. Change the query into a Totals query (there's an icon with a...

Edit box should support languages like japanese,chineese etc
Hi wht im trying to ask is.. 1. In my application, all the labels r in english only. 2.If i change the content of one text box in japanese or chineese language , [Text box value is the title for another dialog] 4.After i changed the content, i want the title to be displayed in japanese or chineese language. Note: I want only that text box to be changed ,,not the entire application.. so How can i able to support multibyte characters in MFC controls CEdit --text boxes.. Plz reply They do. You have to be in a Unicode app, and you have to have selected a Unicode font that has Chin...

Text boxes on graphs
How do you make a text box hide the grid lines behind it on a graph? The grid lines are still visible and run through the text box, cluttering it up. Thanks right-click text box choose -- 'Format Text Box' from shortcut menu colors and lines tab change fill color to white Warm Regards, Crystal remote programming and training Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace * (: have an awesome day :) * Drew wrote: > How do you make a text box hide the grid lines behind it on a graph? The > grid line...

FindFirstUrlCacheEntry() returning NULL Query?
Hi All In my Dialog based Application when the user clicks the button<Delete cache>, i want to delete all the contents of the folder "Temporary internet Files". For this i m trying to use the functions "FindFirstUrlCacheEntry","FindNextUrlCacheEntry" & "DeleteUrlCacheEntry". To start with when i use FindFirstUrlCacheEntry() i m receiving NULL as the return value.this is how i m using it: -------------------------- void CQwDlg::OnButtonDeleteCache() { HANDLE h1 = NULL; INTERNET_CACHE_ENTRY_INFO l1; DWORD dwSize = sizeof(INTERNET_C...

How to display query criteria in my Report?
Hi, I have searched but found no answers, so I guess this is standard :P Anyhow, how do I get a certain fields query criteria written in the report? (For example, my data source is a query that selects all customers (cust_id) with annual turnover gretater than 10,000 (criteria is >10000). Now, sometimes I change the criteria so it would be nice to have it dynamically turn up on the report (rather than static text). But what's the "call function" for this? Kindly, Mikael Mikael Lindqvist wrote: >I have searched but found no answers, so I guess this is standard :P &g...

Multiple Combo Boxes Highlighted
Hi, I have a problem with something in VB6. I have 4 combo boxes with individual names (not an array) located on an SSTab object. When I select another tab and then return to the tab these boxes are located on, each of them appears to be highlighted in blue. I add values to them only when loading the form so I'm at a loss as to why this happens ? Any ideas are greatly appreciated. Thanks, Jen. "Jennifer Ward" <jward@comcast.net> wrote in message news:eq5HrcBrKHA.6064@TK2MSFTNGP02.phx.gbl... > Hi, > > I have a problem with someth...

HELP !! Combo box to feed other combo boxes
Hi, I have a form with a stock list. One field is [Dealer Allocated] so stock can be allocated to a specific dealer - this is a combo box (combo1) for single allocations. I now also want to be able to update this combo box for several records in bulk. I thought I would do this by having a second combo box in the footer of the form which the user selects the dealer from and then clicks a button which looks for first record without dealer allocated and updates the value of combo1 to the value of combo2. This would then be enclosed in a Do Loop for specified number of records. I can...

Why don't my Statement Completion work properly?
Firstly, it does not show the data member or member function other than those defined by me; e.g. if I define class CMyDialog::public CDialog { int m_data; int mFunc(); } Only m_data and mFunc() will appear in the statement completion list. When I copy the entire workspace to another PC, it works very well. Would you please tell me what's the matter? Secondly, when my code grows to certain length, the Statement Completion list does not appear at all! Thanks very much! It could be the .ncb file is corrupted or something or that you have it turned off in install of devstudio b...

Set Text Box Control To Blank
I have a procedure that executes on the beforeupdate event of a textbox (text24). The procedure works out the checkdigit for the number entered and if there is a discrepancy, a message box appears telling the user the number is wrong. What I'd like to do is modify the code as such so that when a discrepancy is found and the user clicks ok on the message box, focus is returned to (text24) and set to blank so that the faulty number is gone. Here is the code I am using.... It's quite crude as I'm only at beginner level, but hey, it works! Private Sub Text24_BeforeUpdate(Cancel As Int...