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

 

[pbaldy]

You'd have to bracket it because of the inadvisable space. I assume the "!" isn't part of the field name, so you don't want that after. You'd also need another single quote after the variable. Try this:

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

Paul
MS Access MVP 2007/2008

 

[HarleyQuinn]

Try:

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

While the Cardholder Naem looks similar to what you've tried I've removed the !

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.

 

[HarleyQuinn]

Too slow...

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]

cool. so a friend helped me out on this and i dont know why he put Tempvar_CCN in the code. CCN is the field in one of my tables, but i dont have a tempvar anywhere. what im trying to do is this:

i have a table with a CCN field in it.
i have a table where a bill imports into every month. there is a field called Cardholdername. in that field there will either be for example a DOE, JOE in one record and a SMITH, JIM in another record. in the CCN field, i have DOE, and SMITH,. So i want Access to look at the CardHolder Name field and say IF CardholderName = CCN, then only show those records. is that code leading me towards the right path?

Valgore

 

[HarleyQuinn]

By the looks of it, TempVar_CCN is a variable designed to hold the value you want to check the CCN against.

By the sound of your data you're going to have to use a LIKE statement (or to match it against a substring of Cardholder Name).

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 looked up like statements and it looks like thats what i want. my noob question is then where would i put it in the code? would i replace tempvar_CCN with a like statement with a wildcard character? from what i read, you have to specify the wildcard character, but can i have multiple wildcard characters?

Valgore

 

[pbaldy]

Try

...where UCase(trim(AmexCurrent![Cardholder Name])) Like '*" & Tempvar_CCN & "*'")

* is a wildcard for any number of characters, ? is a one character wildcard (but you can use more than one).

Paul
MS Access MVP 2007/2008

 

[valgore]

ok i tried that and it didn't filter. i think its the Tempvar_CCN. any other suggestions?

 

[HarleyQuinn]

So Tempvar_CCN is still an empty (unassigned) variable at the moment?

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 believe so. there is nowhere in my code where i set Tempvar_CCN

 

[HarleyQuinn]

So are you wanting the code to check against all CCN's in your second table?

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]

yeah. so my second table has a username field and password field (yes, i know that way isnt very secure). it also has the CCN field. the CCN field has a lastname and a comma, example, DOE,. the amexcurrent is the bill. it has a field with names like DOE, JANE or SMITH, JIM. i want to filter based on who logs in. so if JANE logs in, i want the amexcurrent to only show the charges that have DOE, JANE next to it.

Valgore

 

[HarleyQuinn]

When your user logs (or at another point, if you have the username you can get it the CCN whenever you want) in you could retrieve the CCN for that user and assign it into the TempVar_CCN variable, then your code pretty much 'as is' should work more in the way you're wanting it to.

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.

 

[valgore]

ok so i can do something like

Set TempVar_CCN = Table!CCN

?
and then the other code should work?

 

[HarleyQuinn]

Essentially yes.

It might be easiest to use a DLookup, something like:

TempVar_CCN = DLookup("CCN", "YourUserTable", "Username = '" & currentusername & "'") 'where currentusername is the name of the logged in user.

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.

 

[valgore]

i think i already have that code. i think thats my temprecordset.

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

am i correct?

Valgore

 

[HarleyQuinn]

Yep, just assign the value of tempRecordSet!CCN to your TempVar_CCN variable and I think we might have cracked it

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]

hmm. so i put
Set Tempvar_CCN = tempRecordSet!CCN
in and still nothing... i debugged it, and the

...UCase(trim(AmexCurrent![Cardholder Name])) Like '*" & Tempvar_CCN & "*'")

part works. when i hover over Tempvar_CCN it gives me DOE, Jane or whatever, but temprecordset2 is empty...

 

[HarleyQuinn]

I thought that the CCN field you assigned to TempVar_CCN held just the surname and a comma?

If it's the other way round than I thought (CCN holds DOE, JANE and AmexCurrent holds DOE,) try (when assigning the variable):

Tempvar_CCN = Trim(Split(tempRecordSet!CCN,",")(0))

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.