Hey Guys,

I need to make a query where it shows data which is older than two years.....not the data within the last two years of todays date basically. the data has date field.

Any ideas =]

Cheers Guys

Please post up the SQL you have so far so we can help you figure out where you problem is.

think I have it working now using "<date()-730" 730 being the amount of days in two years does this look right?

I'm using this post since I have an Access Query question too.

I've made a combo box in a form to retrieve data from a query. I want to make this a text box, but I'm not sure how to connect a text box to my query.

SELECT [empcodeq].[empcode], [empcodeq].[emptrade] FROM empcodeq WHERE [empcodeq].[emptrade]=cmbemptrade;

Above is my SQL code. cmbemptrade is a combo box where I select trade and I'm trying to get the employee code from the employee code query. But as I stand right now, I'm using a combo box and I'm selecting from my filtered results. I say result, because emptrade - empcode is a one to one relationship.

Any ideas? Is there a way to select value from this query like an array?
Also I should mention that I have this form connected to a table ( datasheet) and this entered data is being stored there.

Thanks

tommyflint,
You have it right. try hard coding it like this in SQL (Change where it says table to your table name, and field to your field name).


This post has been edited by dlkirk03: 22 Sep, 2008 - 12:25 PM

Limbo
Go to the properties of the dropdown, select Data, Control source should be the same as your query name. Make sure the row source type is set to table/query.

I think I may have worded my question awkwardly. Didn't have my 2nd cup of coffee yet.
The problem I'm having is - I don't want to use a combo box. I want to use a text box.
I've tried using a list box, but the data doesn't get saved into my database.

The display I'm trying to design is

Combo box Text Box


choose value from combo box, and the corresponding value is obtained via a query and put into the textbox

You can’t make a text box a combo box. That being said I would hide the text box set the value of the text box to the selected value of the combo box. (I do this in the VB using the on change for the combo and then in the code the textbox name = the combo box name but a good macro programmer can most likely do it there)
You should really check your control operator (properties, data, control operator) which shold be set to the name of the data field and then it will update that field once you have left the record. You need to keep in mind that the form will not update the database till the form is closed, or is pushed to the next record. If none of this helps Email me with your DB and what exactly you want and I will send you step by step instructions it is a bit difficult to trouble shoot with out the real DB.

Thanks dlk. I think I might take you up on that.
Right now I'm trying to get something working here, so I'm going with the combo box routine.
It all works well till I got to the 6th combo box - query.
It just wouldn't requery. It does work 1 time, but if I want to edit my entry it will not requery.

SELECT [Trade_Q].[ST_Rate], [Trade_Q].[Trade_Code] FROM Trade_Q WHERE [Trade_Q].[Trade_Code]=txttest;

It's as if the query is not resetting the criteria.
Is there a limit to the number of queries you can have in a form?
And if the Trade_Code column has some blanks does it mess up the query?

I think I solved my problem here. There seemed to be a clash between the control source data and the query data. I solved it by splitting the 2 functions into two objects


Actually, I spoke too soon. Sometimes somethings work for 1 second and stop working the next.
I'm still having the requery problem and I'm putting the VBA code in several spots.

ME.cmbsrate.Requery -> TradeCode_Lost Focus, TradeCode_Change, cmbsrate_GotFocus, cmbsrate_Change, cmbsrate_Click.

It's seems I have to click it twice to get it to work (when the requery command is under Sub cmbsrate_Change)
But for everythign else, it don't work. No idea why its not working.

This post has been edited by Limbo: 23 Sep, 2008 - 07:57 AM

Just wondering if there is anyone who might know how to make Access magic.

Still trying to just -

1-Enter Employee Number ( in a combo box)
2-Automatically Retrieve (and/or display) Employee Trade Code from Employee Table
3-Save Employee Number and Trade Code to EmpTrade Table

In Excel, I would use a VLookup function (search employee table for trade code with criteria employee number). All I would do is enter the employee number and I'm done.

In Access, I'm somewhat stuck. I have 2 combo boxes (1 employee, 2 trade). (Employee- Trade is a many to one relationship)
I am able to select the employee number. And I have the query filtering out the employee table for the trade, but then I select the result of the query.
I don't want to have to select the query result. I want it to popup (select automatically) in that object (textbox/listbox/combobox) and then be able to save it to the table.

Is there a code to show/select the first entry of the query or combobox?

Thanks

This post has been edited by Limbo: 1 Oct, 2008 - 08:37 AM