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. 3 followers. Follow

2 Replies
893 Views

Similar Articles

[PageSpeed] 18

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:

How to add a search box to Outlook as a Toolbar??
Hi, We have a search box on our web site. We wanted to integrate this search box into outlook so a user could simply search our site from within outlook. This search should query the database on our side I would assume...perhaps pass the search criteria in the url?? how can this be done? Thx.. -WestSide Anyone know? ...

Filter and Query Records Mismatch
When I perform a filter on a table I get 371 records but when I design a query based on the same criteria, I get 63 records. And the result in my report is not showing the correct data based on this. I can't figure out what I am doing wrong. Any help will be greatly appreciated. Thanks. ...

Data Validation Query:
Hi Guys, First Post. I need help with the following: I have a row of cells that is custom formatted as H:MM 1) Users enter their overtime in hours and minutes (H:MM) What I am trying to do is have data validation that they can only put their overtime H:MM 2) I try and validate their input by going to DATA | VALIDATION | ALLOW | CUSTOM | and custom = H:MM For some reason this will not work. Does anybody know how to get this working? Any Help would be much appreciated. Rgds, Bw --- Message posted from http://www.ExcelForum.com/ You can choose "Time" from the Allow list...

Working with multiple worksheets
This is probably very simple but i am struggling with this. I have a workbook where one sheet will be updated on a monthly basis and i want the sheet preceding it to capture its outputs. A few things i need it to capture is to check if Col B has a specific value and if yes check the value of the cell adjacent to it and count this if it is a Yes or a No. Please help! BK It sounds like VLOOKUP will work for you. Look in the help index for VLOOKUP. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Balaji K. Arige" <v_2balar@hotmail.com> w...

Running "Balance Available" in Query
I'm currently using Access to keep track of the Tuition Reimbursement program at my company. Every two weeks, I produce a report that goes to payroll giving them the amount to be paid, the expended to date balance, balance available, and other irrelevant information. Currently I have a "balance available" field that calculates the annual cap - the expended to date, but it doesn't reflect as a running balance on the report if the employee has multiple entries on a single report. Is there a formula that calculates the running balance available at the query level to be...

Beginner's Append Query Problem
I was trying to follow the instructions on http://www.databasedev.co.uk/reset_autonumber.html but I couldn't choose the table to which I would have appended because that table wasn't on the list in the Append dialog box. Why is this and how to fix? p.numminen@suomi24.fi wrote: > I was trying to follow the instructions on http://www.databasedev.co.uk/reset_autonumber.html > but I couldn't choose the table to which I would have appended because > that table wasn't on the list in the Append dialog box. Why is this > and how to fix? -----BEGIN PGP SIGNED MESSAGE----- ...

Dynamics GP KB site not working
Dear All, I have been trying to access KB for 10 hours, but every time I clicked on any searched article, I got an error message saying "Our Applogise.... An error has occurred while retrieving this document." Is anyone else also experiencing the same problem with KB. Thanks, - Amjad Ali Yes. Frank Hamelly MCP-GP, MCT, MVP East Coast Dynamics www.eastcoast-dynamics.com get your gptip42today at www.gp2themax.blogspot.com Knowledge Base site is up and running again. Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC ht...

Reminders work when Outlook not running?
Can I configure Outlook 2003 so the reminders will still go off even if Outlook itself is not running at the time? -- Thanks in advance... Bob Please don't double post. Answered in Outlook General. "Bob Newman" wrote: > Can I configure Outlook 2003 so the reminders will still go off even if > Outlook itself is not running at the time? > -- > Thanks in advance... Bob > > > No,Outlook must be open to function. Use a third party add-in, such as Magic Mail Monitor. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the disc...

What converter do I need to change my QPW files to work in Excel 2
I had Quattro Pro (about 2003 -2004) on my computer when my hard drive crashed now I have Excel 2003. What converter do I need to change my QPW files to work in Excel 2003? puzzled Maybe the following will help....depending upon version of QP, I believe. You need the Quattro Pro convertors from MS. http://office.microsoft.com/en-ca/results.aspx?Scope=DC&Query=quattro+pro Then read this from Jim Rech http://snipurl.com/cz3x Gord Dibben Excel MVP On Mon, 12 Dec 2005 06:46:04 -0800, "puzzled" <puzzled @discussions.microsoft.com> wrote: >I had Quattro Pro (about...

Query to see what percentage of people scored a specific number
I am fairly new to access and have created a great database. However the queries are now becomming more complicated than I expected. I have two fields (Goal One and Goal Two) the nurse asks the patient have they met their goal. They use a 1-5 rating scale. 1=no, 2=sometimes, 3=likely, 4=most and 5=always. They select the number from a drop down list. I need to see how many patients were given a one, a two, a three etc.... and what percentage of patients were given a three or higher. I need to know this for goal one and goal two seperatly. There are many ways. A relatively simpl...

List Box 01-23-08
I found the property control that will allow me to select several rows of records within my list box. I would like those selected records to print out on a report. How do I distinguish between those that are selected and those that are not and have them print out on a report along with other records that appear on my form? Thanks ...

Cashflow no longer working
Cashflow (in MS Money 2002 Financial Suite UK Edition) is no longer working and I believe its due to a corruption in one of my accounts. An MS Knowledge Base article suggests exporting to QIF and importing to a new account to remove corruption, but this method creates duplicate transfer transactions. I would like to avoid recreating all the accounts affected via transfers, which include many investment accounts and ordinary bank accounts. I've also tried salvage routines but no errors found. Is there another way of identifying a corrupt transaction or has anyone discovered sp...

repaint object not working
Hi. I've got here a strange case. I've got a main form with a chart, and a subform with a chart values. The idea is when I change the values in the subform the chart should automatically change with the new values. An it's working perfectly in Windows XP and Windows 2003 server. The problem is if I use the same database in Windopws Vista and Windows 7 the chart appears always in blank. but if I minimize access and restore/maximize the chart appears with data/lines on it. I already change the windows theme to windows classic. Same result. What can it be? ...

How do I fill data into mfc combo-box control in VS2008
In VS6.0, I could simply type in strings each seperated by Alt+Enter. In VS2008, I cannot input strings in property frame. How do I do that? -- Daewon YOON Type each of the strings separated by a semi-colon in the Property "Data". - Shoaib "TheOne" <daewon.yoon@gmail.com> wrote in message news:559afad2-d866-4798-bb0f-f5d59e3887c1@w8g2000prd.googlegroups.com... > In VS6.0, I could simply type in strings each seperated by Alt+Enter. > > In VS2008, I cannot input strings in property frame. How do I do that? > > -- > Daewon YOON > "The...

How to get query to return total using wildcard
I have a query that has 3 criterion in it as follows: Business Unit, Location, Category. These criterion are all chosen by the user on a loaded form. Currently the results then are based on what is chosen from a drop down list for each of the 3 criterion. The user also wants a total option. Meaning if Business Unit is chosen, and Location and Category are not chosen (or if a wildcard is chosen) the result returns a total based on that Business Unit. How do I write this in the query? You have to use a union query, one part for details and the other for the totals. Use cri...

Query Based DL
I need help please ... I need to setup a Query based DL which fills the list with users based on their CITY field defined in the AD User properties field. I want the QBDList to populate it using 4 different cities. So any user who beflongs to those 4 cities should show up in this QBDL. Any help is appreciated ...Thank you Found my answer here btu partial but still helpful ... http://exchangepedia.com/blog/2007/08/how-to-create-dynamic-distribution.html ... I ran this command on my Exchange 2007 New-DynamicDistributionGroup -Name "TEST DL " -OrganizationalUnit &q...

Msg Box does not return Correct Focus back to Dialog
I have a Dialog based Applcn. on click of Btn1 of Dialog1, a new thread is generated & a Dialog2 is shown In Dialog 2, again of click of Some Btn, Dlg3 is shown In Dlg3, during data validation, when a MsgBox is shown using AfxMessageBox func, the focus does not return focus to Dlg 3, instead focus goes to Dlg 2 Why so Thanks in adv ...

CRMCustomization.Transform query?
Hello, This is a snippet of code I am using in my app. Essentially, what I am trying to do is get the XML string required for the Create method of the CRMSalesOrderDetail object ( In this case, I just need to duplicate a salesorderdetail record given a salesorderdetailid) CRMCustomization cCustObj = new CRMCustomization(); cCustObj.Credentials = credCache; cCustObj.Url = string.Concat(text3, "CRMCustomization.srf"); cObj = new CObjectName(); cObj.Id = "{C75F6177-7F80-44BA-BE39-3FF5854A981D}"; cObj.Type = Microsoft.Crm.Platform.Proxy.ObjectType.otSalesOrderDetail ...

Query from three tables, problem when adding a record
I'm using Access 2002/2003. I have three tables, A, B and C. A has a primary key, which is linked to corresponding foreign keys in B and C. In each table the key values are unique. I have a query Q showing all rows from A linked to the corresponding rows from B and C. Calling Q, I can add a new row to A. I can also add a new row to B and a new row to C at the same time. But in case I add a new row only to A and then close the query, opening the query again does not allow me to add the corresponding new rows to B and C. Acces refuses to store these new rows. Can I modify the query to...

Populate comboboxes based on query
I have a textbox on a form that user will enter a string into and press a button. An example of what they will enter is "BD532313CW". Once they press the button i take the first three numbers (532 in this case) and assign them to a string variable named myString. Next I have 2 combo boxes on a form named cmbAcct and cmbDisp. I have a table named "Swbs" that has three fields in it Swbs.Acct, Swbs.Disp, and Swbs.Num. There are duplicates in all fields. I need to populate cmbDisp with the distinct data in Swbs.Disp where Swbs.Num = myString. Likewise I need to populate c...

How time work with a file excel ?
Hello from Italy , i have an old five of excel (five years old) . I work with this every day , now i have the curiosity to know : how many hours it was open ? TIA Hi if I understood you correctly there is no chance to get this information :-) -- Regards Frank Kabel Frankfurt, Germany barbun wrote: > Hello from Italy , > i have an old five of excel (five years old) . > I work with this every day , now i have the curiosity to know : how > many hours it was open ? > > TIA Arbun; This is possible with a VBA macro, but I have to write that. Maybe google can serve you bett...

Error Exporting Access Query Results to Excel
I'm having 2 different problems exporting query results from Access to Excel (Office 2007). Problem 1: I do what I've always done, in Access VBA get ADO recordset from query results, instantiate Excel, get a range object, use the range object's CopyFromRecordset method. This has always worked in the (pre-Office 2007) past for me. It is much easier than iterating records and columns and assigning values to cells. Now, I get an error -2147467259 Automation Error Unspecified Error. I'm guessing this is because 2 of my recordset fields are pictures in Attach...

SSRS 2008 working with table of contents
I have the following questions about creating sql server 2008 reports: I am creating a report that will contain the following: a. page header with the title of the report, b. page footers that contain the page numbers, and c. table of contents to tie about 27 subreports together, d. several of the tables in the subreport will appear on more than one page. Thus, I would like to know how to tie all of this together. I am planning on doing the following: 1. Put the title of the report in a textbox control, 2. Somehow have logic in the tables to know to wrap to the next ...

Work hour tracing program
Hi, Could anyone recommend a work hour tracing excel sheet for me? I like to find a good and free one. I should be able to define tasks and log tasks in hourly wise. Thanks. -- Life is the only flaw in an otherwise perfect nonexistence -- Schopenhauer narke On 2010-05-25, narke <narkewoody@gmail.com> wrote: > Hi, > > Could anyone recommend a work hour tracing excel sheet for me? I like to > find a good and free one. I should be able to define tasks and log tasks > in hourly wise. > > Thanks. > any result? -- Life is the on...

sql aggregate query
My table ID - Autonumber Field Patientid - This can have a number of records with the same number dDate is the date of the record and can be duplicated. I need to create a new table with the max of date for each unique patient id. So far not a problem. SELECT TblCurrentEncounter.Patientid, Max(TblCurrentEncounter.dDate) AS MaxOfdDate FROM TblCurrentEncounter GROUP BY TblCurrentEncounter.Patientid; But the kicker is that i need the id of the row containing the max of date. As soon as i try to add ID to the query I get error about not being part of the aggregate function. Any ideas w...