Search results

Ahhhh Ok, this gives me a solution to an existing problem also Brilliant, Thanks Minty

Thanks Minty. :) That worked, but why? I've always used Integer when referencing ID fields.

I have hit a barrier while using access as a front end to my database in that there are now more than 32768 records in my ITEMS table Most of the time I can get around this issue however I'm INSERTing into the SQL table via a button on an Access form which works ok but I then need to get the ID...

I have now finished the form which is working perfectly. Thanks for all your help Minty :)

It worked! I had this version earlier and the only difference is one set of brackets. Why does VBA need to be so 'brackety'! Now to get it to use the variables from the form.. Thanks Minty (though I might not be finished quite yet lol) UPDATE ((Item INNER JOIN Job ON Item.JobID = Job.ID)...

Ok the following works as a select SELECT Job.JobNumber, Item.ID AS ItemID, ItemType.ItemCategoryID, Item.JobID FROM ((Item INNER JOIN Job ON Item.JobID = Job.ID) INNER JOIN MakeAndModel ON Item.MakeAndModelID = MakeAndModel.ID) INNER JOIN ItemType ON MakeAndModel.ItemTypeId = ItemType.ID...

Still syntax error (see attached) Tried also without the extra bracket after 13

A fresh Monday morning try at this now :) REbuilt the query in designer as a SELECT and retrieved the correct records. SELECT Job.JobNumber, Item.ID AS ItemID, ItemType.ItemCategoryID, Item.JobID FROM ((Item INNER JOIN Job ON Item.JobID = Job.ID) INNER JOIN MakeAndModel ON Item.MakeAndModelID...

GAAAAAh This is what I have! All Access says is "Syntax error" but doesn't give a clue to what the error is! UPDATE ITEM (((INNER JOIN Job ON Item.JobID = Job.ID) INNER JOIN MakeAndModel ON Item.MakeAndModelID = MakeAndModel.ID) INNER JOIN ItemType ON MakeAndModel.ItemTypeId = ItemType.ID)...

SQL Server Management Studio Damn, I thought that it'd just pass the query direct through to SQL server. No doubt that's where I'm going wrong. Gah

Same problem Static! See the screenie. This is driving me bonkers. I can see nothing wrong with it! Also, I needs the "FROM Item" if running it in SQL studio, maybe VBA is different

Ok I ran the following line in SQL management studio (which worked fine) UPDATE Item set JobID = 8861 FROM Item INNER JOIN Job ON Item.JobID = Job.ID INNER JOIN UsedIT ON Item.ID = UsedIT.ItemID INNER JOIN MakeAndModel ON Item.MakeAndModelID = MakeAndModel.ID INNER JOIN ItemType ON...

Static: Ran it in the immediate window but unsure what to do thereafter. Copied it into the window where the code is and ran that as strSQLfinal but got the same surrounded the strSQLfinal with single quotes as below strSQL1 = "'UPDATE Item set Item.JobID = " strSQL2 =...

I tried that previously Minty and again now. The first shouldn't be a string anyway. Its probably sometihng to do with VB/SQL single & double quote marks

Looking for some help here please. Using msgbox it looks like my SQL statement is correct here but it's throwing up the 3075 error (see attachments) I've tried the SQL code as a 1 line like: strSQL1 = "UPDATE Item SET JobID = '" & Me.cboTargetJob.Value & "' " & "FROM Item INNER JOIN Job...

Informer, worked first time! A HUGE thanks from me. :) Don't suppose you can spare the time to explain it a little though? Cheers

Fantastic Informer, I will try this today and let you know how I get on. The code for the ConstructQuery function is: Public Function ConstructQuery(ByVal sql As String, ParamArray args() As Variant) Dim segments() As String Dim i, px, closeBrace As Integer Dim Result, s, v As...

I'm trying to modify a VB script which does the following. The purpose of this is to add items to an order. When an excel file is selected the script reads the file and updates the SQL database for lines which match the ItemID by updating the sql Item table with the OrderID. (it also checks the...

Sorry, not sure what you mean =Count([QTY Sold])<>0 doesnt work Where is the criteria property?

I have a field which contains a count "=Count([QTY Sold]) " Some of the fields have 0 as the data and it seems to include this value in the count. How do I stop it counting a 0 as a value? THanks Paul