Search results

I have a query that does an inner join. I want it to just pull in the first match from the second table in the event there are multiple matches. How do I limit the query to only pull in the first match found for the rcust.CustID? Thanks for any help. :confused::confused: SELECT...

Bob, Thanks for the quick reply. I can't modify any of the existing tables is the reason for me wanting to throw it in a separate table. We are trying to pull some data into a script that generates an XML page on the fly, but the client is using a really crappy ODBC drive that doesn't support...

Okay, I have been screwing around with Joins and Unions and can't quite get this right. I have one table I am trying to update with information from 2 others, but I can't get this working. I need to say something like this: UPDATE Table1 SET Table1.CustId = Table2.CustId, Table1.FirstName =...

Sweet, that was it. Worked perfectly. Updated all 65k records. I really really appreciate your help! :D:D:D

Yeah, I tried filtering out NULL with no luck. How would I say if it contains a space? UPDATE Users INNER JOIN UserIdInfo on Mid(Users.ID, (InstrRev(Users.ID, " ") + 1)) = UserIdInfo.OldUserId SET Users.ID = Mid(Users.ID, 1, (InstrRev(Users.Id, " "))) & [UserIdInfo].[NewUserId] WHERE Users.ID...

Okay, so I am running into a problem. I ran this in a test environment on my machine and it worked fine with a small sample of data in Access 2007, but when I ran it on the production environment on Access 2003 I got the following error. Data Type Mismatch in criteria expression I checked and...

pbaldy, thanks for the info. That worked perfectly. Here is the complete update statement. Got to use this trick twice!!! Thanks again. UPDATE Users INNER JOIN UserIdInfo on Mid(Users.ID, (InstrRev(Users.ID, " ") + 1)) = UserIdInfo.OldUserId SET Users.ID = Mid(Users.ID, 1...

Ah, I see what you are saying Mid(UVAR, InstrRev(UVAR, " "))

It is unfortunately not always 5 digits. It starts with 1 and goes up to 565000 with no leading zeros.

I have a table that someone created that for whatever reason they put the text CustID= into the UVAR field instead of just the ID number. The other thing that I noticed is that there several spaces between CustID= and the customer number. So, it may be in one time as CustID= 12345 and the next...

Thanks for your help Tim. Does my syntax look okay through this section though? IF Right(Me![SelectAuction], 2) = FR Then Me![BuyerFees] = 200.00 Else 'calc Buyers fee Me![BuyerFees] = DLookup("BuyerFees", "qryBuyerSpecialFees_SaleID") If...

Or will something like this work? getterms ' Calculate Fees Dim x As Variant IF Right(Me![SelectAuction], 2) = FR Then Me![BuyerFees] = 200.00 Else 'calc Buyers fee Me![BuyerFees] = DLookup("BuyerFees", "qryBuyerSpecialFees_SaleID") If...

Unfortunately they already did the FR part on their own. :S Okay, I am going to attempt this one piece at a time. It looks like Dim declares a variable. So, would this work to isolate the last two characters and put them in a variable SpecialSale? Dim SpecialSale As String SpecialSale =...

I have a client with an access 2003 database and I need to change something for them. I normally work in php mysql so I am a little out of my element. They are submitting a form that processes and checks for fees. They have a sale number that is normally based on the date. 20080612 for...