error setting a tempvar 2

[valgore]

So i have two temp vars. one called tempRecordSet and the other is called tempRecordSet2. tempRecordSet2 doesn't work. i know why, but i dont know how to fix it.

Set tempRecordSet2 = CurrentDb.OpenRecordset("select * from AmexCurrent where UCase(trim(AmexCurrent!Cardholder Name!)) = '" & Tempvar_CCN)

it is erroring out on UCase(trim(AmexCurrent!Cardholder Name!))
it doesnt like the space between Cardholder and Name. what do you use to tell Access that thats actually one field? i have tried using "Cardholder Name"! and [Cardholder Name]! but those dont work.

any help would be appreciated

Valgore

 

[valgore]

No. CCN can hold either DOE, or DOE, JANE. it doesnt matter. AmexCurrent hold DOE, JANE - plus last 6 digits of credit card number. there will only be max 7 different names in AmexCurrent

 

[HarleyQuinn]

So if you just run the query:

select * from AmexCurrent where UCase(trim(AmexCurrent![Cardholder Name])) Like '*DOE, Jane*'

Do you get any results back?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[valgore]

nope. i didnt filter AmexCurrent. do i need a code that says filter?

just thinking..
Valgore

 

[valgore]

*IT didnt filter...

 

[HarleyQuinn]

I shouldn't have thought so.

Can you post a couple of EXACT samples of the data in the CCN and [Cardholder Name] fields so we can possibly see why we can't match them?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[valgore]

i cant post actual data, but i can post most of it with dummy names.

UserName	Password	SecurityCode	        CCN
Admin	         blah	          1	
Jim	         blah2	          3	      DOE,
Jane	         blah3	          5	      SMITH,
John	         blah4	          4	      BROWN,
Ken	         blah5	          6	      WHITE,
Josh	         blah6	          2	      BLACK,

this is table UNP above

ID#	Cardholder Name	   Process Date  Vendor	  Amount
AUTO#	WHITE, KEN - 65837      16-Dec	Staples	   3.4
	WHITE, KEN - 65837	  16-Dec	wallmart   12.56
	BLACK, JOSH - 68120		   apple	   100.234

this is table AmexCurrent above

Set tempRecordSet = CurrentDb.OpenRecordset("select * from UNP where UCase(trim(UserName)) = '" & UCase(Trim(Forms!LogonScreen!User)) & "'")
Set Tempvar_CCN = tempRecordSet!CCN
Set tempRecordSet2 = CurrentDb.OpenRecordset("select * from AmexCurrent where UCase(trim(AmexCurrent![Cardholder Name])) Like '*BLACK, JOSH*'")

this is all the variables

sorry if it looks weird. i dont know how to upload an excel file without paying at box.net

 

[valgore]

wait. now for some reason that query works

 

[valgore]

so i made the query with the code

select * from AmexCurrent where UCase(trim(AmexCurrent![Cardholder Name])) Like '*DOE, Jane*'

and it works. so im wondering why it wont work in the code....

Valgore

 

[HarleyQuinn]

When you get to the version of the query that doesn't work in the code before you use OpenRecordset could you assign the full query to a variable and use a Debug.Print to see what the actual query value is and post it back?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[pbaldy]

If you're using an ADO recordset (rather than DAO), you'd need to use % as a wildcard instead of *.

Paul
MS Access MVP 2007/2008

 

[HarleyQuinn]

Can you use ADO recordsets with CurrentDB.OpenRecordSet?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[valgore]

Im almost positive im using DAO because i use * in other code. your post HarleyQuinn went right over my head...

The query that i posted works perfectly. it works if i put DOE, or DOE, JANE. but it doesnt work when i put the Tempvar_CCN... as for the Debug.Print comment, ive never used that so i have no idea what your talking about .

Sorry...
Valgore

 

[HarleyQuinn]

Sorry, the ADO recordsets post was in reply to pbaldy's post and I should have stated that.

Try this:

Dim TempSQL as String
TempSQL = "select * from AmexCurrent where UCase(trim(AmexCurrent![Cardholder Name])) Like '*" & Tempvar_CCN & "*'"
Debug.Print TempSQL
Set tempRecordSet2 = CurrentDb.OpenRecordset(TempSQL)

Put a breakpoint on the line:

Debug.Print TempSQL

Once the code stops, open the Immediate Window (Ctrl+G) then step to the next line (F8), the SQL you are trying to execute will be written to the Immediate Window.

From there you can try and run that SQL in Access and post how that goes (you could also post the value here so we can see).

Cheers

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[valgore]

ok i will try that. i will be back at work tomorrow morning so ill try it tomorrow and post back.

Thanks

Valgore

 

[pbaldy]

HarleyQuinn, as you know, the original posts were DAO. I only mentioned the ADO thing in case the OP had switched methods somewhere along the line, since I've seen that happen before. Sometimes when someone is searching for a solution, they end up copying and pasting code from different places, so I felt there was a possibility Valgore had switched. Your suggestion of printing out the finished string was a good one, and will probably lead to the problem.

Paul
MS Access MVP 2007/2008

 

[HarleyQuinn]

Paul,

Agreed, I can see the logic in your ADO question.

Hopefully printing out the string will lead to the solution, I can imagine Valgore getting pretty frustrated with how long this one's been going on now...

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[valgore]

HarleyQuinn, i am not at all frustrated. i have no due date on this. i am actually enjoying learning code. anyways. i put in the code and put a breakpoint on Debug.Print TempSQL and i stepped in. this is what was put in the Immediate window

select * from AmexCurrent where UCase(trim(AmexCurrent![Cardholder Name])) Like '**'

thank you both SO much

Valgore

 

[valgore]

oh. so this is all my code for this problem so far

 Dim TempSQL As String
TempSQL = "select * from AmexCurrent where UCase(trim(AmexCurrent![Cardholder Name])) Like '*" & Tempvar_CCN & "*'"
Debug.Print TempSQL
Set tempRecordSet2 = CurrentDb.OpenRecordset(TempSQL)
    'this sets the variable
Set tempRecordSet = CurrentDb.OpenRecordset("select * from UNP where UCase(trim(UserName)) = '" & UCase(Trim(Forms!LogonScreen!User)) & "'")
Set Tempvar_CCN = tempRecordSet!CCN
Set tempRecordSet2 = CurrentDb.OpenRecordset("select * from AmexCurrent where UCase(trim(AmexCurrent![Cardholder Name])) Like '*DOE, JANE*'")

the tempRecordSet2 right below the Debug.Print says its empty still....

 

[HarleyQuinn]

It will, you've not set TempVar_CCN when you use that.

What does this output:

Dim TempSQL As String

Set tempRecordSet = CurrentDb.OpenRecordset("select * from UNP where UCase(trim(UserName)) = '" & UCase(Trim(Forms!LogonScreen!User)) & "'")
Set Tempvar_CCN = tempRecordSet!CCN

TempSQL = "select * from AmexCurrent where UCase(trim(AmexCurrent![Cardholder Name])) Like '*" & Tempvar_CCN & "*'"
Debug.Print TempSQL

Set tempRecordSet2 = CurrentDb.OpenRecordset(TempSQL)

What does the Debug.Print output now?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[valgore]

select * from AmexCurrent where UCase(trim(AmexCurrent![Cardholder Name])) Like '*DOE, JANE*'

thats what it gives me in the immediate window. i put DOE, JANE in place of what was really there because it has credit card number in it. but it gave me exactly what was in the cardholder name field

looks promising!!!!!
Valgore