How do i change a query in Excel to Access


I have the worksheet setup through the data import to pull a query from a database and it works great. The problem is each time i run the sheet i have to go into access and change the number of the query i want to pull before i do the import. I have a feeling its with a sql string but im not sure i've never done it before.

How can i pass that number from excel into the query so it only returns the test results for the project number i want instead of going into access to modify the query each time just to change one number??

Right now all that shows in VBA is


	Code:
	Selection.QueryTable.Refresh BackgroundQuery:=False

I need to be able to modify the WHERE part of this query. The data im pulling is based on an test number. Each time i get a notification a test is done i go into the database and change the query in design view and just put the test number in the "Criteria" so that only that tests results are returned. That criteria is what i want to pass from excel to eliminate the going into access at all.

Thanks for any help, it is appreciated.


Sponsored Links:



I hope I'm going about this in the correct way?
I am trying to teach myself access 2007 but the following problem defeats me. I set up two tables; PRODUCTS which includes Product Name and price, and ORDERS - which uses a lookup field to select the product. I also created an Order Form from a query which selects the price from the products table so far so good. However, when I change a price in the product table, it changes not only the current order record but also all the previous records. I have tried various ways of locking the price field on the form but to no avail. What am I doing wrong? Any help would be much appreciated.
JCJ




How do I design a query that returns all records that have one or more null fields in them. Or one that returns records if one of a predetermined subset of the fields is null?
I can't for the life of me work out how to do it so any help would be welcome.

TIA
Tom




Hi,

I have two separte tables in my database, each have the same fields but one table holds current data and the second expired data. How do i get a query to count the total for both tables in an overall total

Thanks




I have a form which allows user to add records which is called addcommitmentdetail. It has a textbox on top that says " Add Commitment". However, when i open the form in edit mode ( I have created a separate form that has a listbox that displays data and users are able to open the form based on selection) how do i change the words in my textbox on top of the form to " Edit commitment "?

Also,
How do i display the records in datasheet view once select a record from the listbox? This is my code i used in a button to display the form :

DoCmd.OpenForm "AddCommitmentDetail", , , "CommitmentID=" & Me.ListShowCommit.Column(3)
DoCmd.Close acForm, "SelectEditCommitment"

but this is only to open to form with the related information




How do I delete a record in form view using the default record navigation bar?

I have set allow deletions=true

Thanks




Hi everyone,

I have a form that I spent a very long time formating to look nice and pretty. Well, low and behold, a bunch of data changed and I had to make a new table. Before I had a bunch of table and queeried lot the info I wanted. When the additional data came my way, I decided to dump everything into excel, combine the 7 tables into one. But now I have this wonderful form for data entry but it is attached to the old query. How can I change the query the form pulls from? I hope I am making sense here.

Also, I have another table that I pulled in as alink table (another dept's data). How can I incorporate that one in so when they update their data, my access data is updated too. Is that a subform? Or what, and how do I use it?

Thanks in advance,
Lana




I want to use a query in a text box.

I have to use this query to give a total of a column ina table.

Is there an easier way then a query

The only way I know how to use a query is in a list.

Thanks




I have a query "Sort String: Drawing.Size & "-" & [Drawnr]" that I want to use in a form. I havent succeded to get it to connect succesfully.
If I use ="A" & [Size ] & "-" & [Drawnr] as control source you cant right click and choose descend or ascend. How should I solve this?




Dim Sqlstr1, Sqlstr2 As String
Sqlstr1 = "create table Fbal;"
Sqlstr2 = "SELECT IL4010DF.* INTO Fbal FROM IL4010DF ORDER BY IL4010DF.Pnumber;"
DoCmd.RunSQL Sqlstr1
DoCmd.RunSQL Sqlstr2
CurrentDb.TableDefs.Delete "IL4010DF"

Call CreatePKIndexes("Fbal", "Pnumber", "Fund_code")

This creates a table with name "Fbal" in my own database.
How do I create this table in a new database? (I want this done because the file will be very large, more than 1GB)

Thanks!




Hi all,

I've got a query which calculates the total applications received between two dates, and the value of these applications. At the moment, I'm running the query 13 times-as an update table query which gives me the totals for a year.

How can I make the query run, append to the table, and then run repeatedly each time prompting me to input the period start and end dates? It's really just the 'repeat' part I need, as I know how to get Access to prompt me for the dates.

Just to round out the picture, I plan to build tables for the past couple of years, and then use graphs to compare year on year. I haven't really figured how to do this yet, but thinking that keeping the Count of warrants and Value of work graphs separate would be best? Any pointers gratefully accepted!

Thanks

FrankyG




hi im currently trying to complete an access project involving a music player creation.

i've created a form to search through the database for specific songs (e.g by title, artiste etc.). how do i create a subform in the form so that users can type in their search in the form and see the results in the subform just below?

thanks alot for the help )




I would like to lock a field after data has been entered to prevent users from changing the data. I would also like to include a message box that asks users whether the information entered is correct. How do I go about this?

PS: New to Access, first real DB attempt, detailed information will be appreciated




I've been looking for a way to do this unsuccessfully. I'm running Access 2007 on Vista. How do I change a command button's Picture on a Mouse Move and Mouse Down...creating a rollover effect.




A fellow employee created a table that has ss#s. Different people have been entering the data. Some added the dashs others did not. How do I run a query that checks to see if the 4th character and the 7th character are dashs and if they are not insert them so all the ss#s will have the same correct format?




I have a query in my database, and I have recently added new possible entries for area. My report tells me how many male chickens vs. how many female chickens I have. Now with the new entries for area, I want my breakdown to EXCLUDE any chickens that live in a specific area.

For instance:

Pen A has 15 males & 15 females.
Pen B has 12 Males & 13 Females.
Pen C has 50 males & 50 females.

Pen C is the recent addition...

Right now my report shows: 77 Males & 78 Females...

I WANT it to EXCLUDE Pen C, so it would report: 27 Males & 28 Females.

Can anyone tell me how best to do this? I figured I would add a custom field to the query that the criteria for is "Pen = C" and then on my report I would change the data source from: =Count([Chicken Query]![Male] --- and make it =Count([Chicken Query]![Male] - Count([Pen="C"]) --- or something like that.

Can anyone help me? And if so could you help with the proper code for the second part that I am adding new? I know the Count([Pen="C"]) isn't right, but until I know exactly how to add a custom field to the current query, with the critieria of Pen="C", I don't know how to write the -Count([NewField]) code, if that would even work.

Thanks!




Currently I have a Module in Excel that works great but I'd like to have it fire when the user changes a value in one of thesecolumns (E:I). I created a Worsheet_Change Module but can't think of the syntax. The only code I know is Selection.Address but not sure how that fits in to to the Worksheet Change Event. Any ideas to get me started would be great.

Thanks




I i open a query using

With DoCmd
.SetWarnings False
.OpenQuery "myQuery"
.SetWarnings True
End With

How do I close it when I'm done

I tried

.close acquery "myQuery" according to the example, but the results are still open.

Also, while it is open, haow can I refer to the results in order to change or work with them?






How do I change the name of a field in a table. Say that the name of the first field is [Employees ID] and I want to change it to, say [Employees].

I have tried the following: -

rst.Fields(0).Name = "Employees"

However it comes with a run-time error of 3219 (Invalid operation).

Is this because it's a read only property and hence can't be changed programatically?




Hi guys,
I'd really appreciate some help with the following. The bottom line is the function HmhCostCalc (h,d,depth) returns a cost which is then inserted into the appropriate field via an update query. The user determines if it's field A and A_Cost, B and B_Cost etc. They also supply h and d (args for the function). However depth is not a field on the form and the update happens to a group of assets at a time.

The line with the issue is:

strMH = "UPDATE tbl_MHRecommendation INNER JOIN tbldef_updtMH ON tbl_MHRecommendation.Manhole_ID = tbldef_updtMH.Manhole_ID " _
& "SET " & yr & " = '" & h & "'," & yC & " = " & HmhCostCalc(h, dia, Manhole_Depth) & ";"

specifically Manhole_Depth.

Q: How do I pass a field, Manhole_Depth, from the table tbl_MHRecommendation to the function HmhCostCalc?
If I use it as pasted above, I get no error but the function returns a cost of 0 (meaning the value passed is Null);
If I use tbl_MHRecommendation.Manhole_Depth, I get err 424 'Object required';
If I enclose it in [] -> [tbl_MHRecommendation].[Manhole_Depth] - the module will not compile and I get - 'external name not defined' error;
if I substitute a number ex. 5, the correct costs are calculated and the records are updated.

Therefore, I think, my problem lies in how to pass that value in that field to the function when I call it here.

I did make the assumption that a sql statement in a module would work like it does when in a query design window - as long as the field name is valid and I supply the correct args, the update will occur. Is this incorrect?

Any pointers, corrections, advice etc is appreciated and thank you very much for your time.