Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

error setting a tempvar 2

Status
Not open for further replies.

valgore

Technical User
Nov 12, 2008
180
US
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.
Code:
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
 
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.

 
Here's a crazy idea [wink], Try this:
Code:
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.

 
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.
 
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.

 
that works too. but doing it this way wont work for what we want i dont think.
 
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...
 
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.

 
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
 
[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 ...
 
[smile] thx for the post OzFoxy, but i think HarleyQuinn solved my problem.

Valgore
 
Valgore - glad I could help, thanks for the star [smile]

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.

 
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
 
Something like:
Code:
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.

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

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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top