Link to a query 1

[ironj32]

I have the following query set up:

SELECT [Account Information].AccountNbr
FROM tblVendorID INNER JOIN [Account Information] ON tblVendorID.VendorID = [Account Information].VendorID
WHERE (((tblVendorID.VendorID)=[Forms]![frmNewAcctSearch]![VendorID]));

I have an AccountNbr i.e. 9223-MSP-1234

I would like to have this number as a link to run another query. The deal is, is that I want the query to search for any record that has 1235 in it (i want it to add 1 number to the last four digits).

Any thoughts?

 

[PHV]

A starting point:
WHERE [Account Information].AccountNbr Like '*-' & (1+Val(Mid(yourQuery.AccountNbr,InStrRev(yourQuery.AccountNbr,'-')+1)))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

or perhaps:

[blue]WHERE ([Account Information].AccountNbr Like '*-*-' & CStr(CInt(Right(yourQuery.AccountNbr,4))+1));[/blue]

See Ya! . . . . . .

 

[ironj32]

thanks guys! what i did was put this query (qryVendorIDAcct) in a subform (frmVendorIDAcctQrySub) and then set the AccountNbr field as a hyperlink to the new query: qryAcctPLUS1

this is the SQL for qryAcctPLUS1

SELECT [Account Information].AccountNbr
FROM [Account Information]
WHERE [Account Information].AccountNbr Like '*-' & (1+Val(Mid(frmVendorIDAcctQrySub.AccountNbr,InStrRev(frmVendorIDAcctQrySub.AccountNbr,'-')+1)));


it links to the new query alright, but i have to type in the AccountNbr by hand...i'd like to get it to put in the number automatically.

thanks again for your help!

 

[ironj32]

never mind...i figured it out here is what i ended up using.
thanks again.

SELECT [Account Information].AccountNbr
FROM [Account Information]
WHERE ((([Account Information].AccountNbr) Like '*-' & (1+Val(Mid([Forms]![frmAcctSearch]![frmVendorIDAcctQrySub].[Form]![AccountNbr],InStrRev([Forms]![frmAcctSearch]![frmVendorIDAcctQrySub].[Form]![AccountNbr],'-')+1)))));