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

 

[HarleyQuinn]

Hmm ,that's exactly what it should be saying.

And you're getting records where the AmexCurrent![Cardholder Name] isn't like DOE, JANE?

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.

http://lessthandot.com

 

[HarleyQuinn]

Here's a crazy idea , Try this:

DoCmd.OpenForm "YourDataFormName", acNormal, , "[Cardholder Name] Like '*" & Tempvar_CCN & "*'"

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.

http://lessthandot.com

 

[valgore]

no. it works perfectly. i log in as DOE, JANE and it filters on DOE, JANE. if i log in as BROWN, JIM, it filters on BROWN, JIM. my quesiton is how do i then move that filter so it works on AmexCurrent.

 

[HarleyQuinn]

Why don't you just amend the data on the form?

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.

http://lessthandot.com

 

[valgore]

that works too. but doing it this way wont work for what we want i dont think.

 

[valgore]

so your saying use the Form for each person and then you can transfer the charges from the form to AmexHistorical? i am SO confused now...

 

[HarleyQuinn]

Ah right.

I've got to go now but I'll be back on Monday to check up on developments

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.

http://lessthandot.com

 

[valgore]

ok i was way too confused and i had my IT manager come in and explain it to me. it works perfectly. it works just the way we want. i don't think we need anything else.
i want to thank you SOOOOOOOO much for all of your help. you have no idea how frustrated i was until you helped me

Valgore

 

[valgore]

have fun at the pub!

 

[OzFoxy]

[Cardholder Name] Like '*" & Tempvar_CCN & "*'"

I believe with this code you will run into problems with names containing an apostophe e.g. O'Sullivan, where the result will be: [Cardholder Name] Like '*O'Sullivan*'

This may be preferred: "[Cardholder Name] Like ""*" & Tempvar_CCN & "*"""
To produce this: [Cardholder Name] Like "*O'Sullivan*"

Or have I barked up the wrong tree? Happy to be corrected ... I try to learn at least one new thing a day - considering how little I know, that's easy ...

 

[valgore]

thx for the post OzFoxy, but i think HarleyQuinn solved my problem.

Valgore

 

[HarleyQuinn]

Valgore - glad I could help, thanks for the star

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.

http://lessthandot.com

 

[valgore]

ok... one last question. should be pretty easy. all i want to do is say if CategoryCode field in AmexCurrent has something in it, i dont want it to show on that form we made.

Valgore

 

[HarleyQuinn]

Something like:

DoCmd.OpenForm "YourDataFormName", acNormal, , "UCase(trim(AmexCurrent![Cardholder Name])) Like '*" & Tempvar_CCN & "*' and AmexCurrent![CategoryCode] = ''"

Hope this helps

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.

http://lessthandot.com

 

[valgore]

i didnt work. i didnt give me any records, but thats ok. i need to move on from this part
thanks for trying

Valgore

 

[valgore]

i hate to restart this thread again, but i encountered a minor problem. when i log in and click the categorize charges button, it goes to the form and there isnt any data in the fields. if i take the filter off and put it back on, it filters fine. any ideas?

Valgore

 

[valgore]

nevermind