Compare with table values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello

I have a table with a field ("Name"). I would like to compare the CurrentUser() against all of the values in the "Name" column. How can I do this

Basically I would like to create something along the lines of
if currentUser() <> ................... the

Daniel
 
Perhaps you can use the Domain functions.

I would probably use the DCount function in this case.

If DCount("*","TableName","[Name]=""" & CurrentUser() & """") = 0 Then
'Not there so do something
Else
'At least one match, so do something else
End If
 
John,

I tried to implement your code for my specific case and I can't seem to get it to work. In my case the [Name] column contains the frist name followed by the last name of each individual for the comparison purpose I need to transform it so that I compare the currentuser() with the first letter of the first name followed by the last name.

Example
Name column contains Julian Pare but for the comparison I need jpare to be compared with the current user.

I transformed your code as shown below:
If DCount("*", "Stress Tbl", "left([Name],1)" & "Right([Name], len([Name])-Instr(1, [Name], " "))=""" & CurrentUser() & """") = 0 Then
but can't get it to work.

Any help is appreciated.

Daniel
 
John

I fixed one issue with the following mod to your original code

If DCount("*", "Stress Tbl", Left([Name], 1) & Right([Name], Len([Name]) - InStr(1, [Name], " ")) & "=" & CurrentUser() & "") = 0 The

but when it gets run the [Name] value is not that of the names in my table but rather the name of the subform form where the event occurs?! I'm confused the second section of the DCount function clearly states to use the "Stress Tbl"...

Any Ideas

Daniel
 
Well, now you know why it is a bad idea to use NAME as the name of anything in
Access. Every object has a name property. You must disambiguate what you are
referring to. Also, in your case you need the field name in the table.

PERHAPS the following might work, but I'm not sure you can use VBA functions
inside a DCount function

If DCount("*", "Stress Tbl",
"Left([Name], 1) & Right([Name], Len([Name]) - InStr(1, [Name], "" "")) =" &
Chr(34) & CurrentUser() & Chr(34)) = 0 Then

Try it. If that still doesn't work, post back again.
 
Back
Top