By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,454 Members | 3,103 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,454 IT Pros & Developers. It's quick & easy.

dlookup and like error

P: 18
Hi,
firstly, here is my code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub paragelia_BeforeUpdate(Cancel As Integer)
  2. Dim varTemp As Variant
  3. Dim varTemp1 As Variant
  4. Dim sFilter As String
  5. Dim inputtext As Long
  6. Dim astrWhere As String
  7.  
  8.  
  9. inputtext = InStr(1, Forms![frm_kataxorisi]![paragelia], "-")
  10.  
  11. sFilter = Left(Forms![frm_kataxorisi]![paragelia], inputtext - 1)
  12. varTemp = DLookup("[ypiresia]", "tbl_kataxorisi", "[ypiresia] = Forms![frm_kataxorisi]![ypiresia]")
  13.  
  14. varTemp1 = DLookup("[paragelia]", "tbl_kataxorisi", "[paragelia] like 'sFilter'")
  15.  
  16.  
  17. If varTemp = Forms![frm_kataxorisi]![ypiresia] And varTemp1 = Forms![frm_kataxorisi]![paragelia] Then
  18. Me.Undo
  19. MsgBox "Η Τιμή αυτή έχει ήδη καταχωρηθεί", vbOKOnly, "Προσοχή Διπλότυπη καταχώρηση"
  20. Else
  21. MsgBox ("hi")
  22. End If
  23.  
  24. End Sub
I manage to take the value before (-) with sFilter(string), but i cannot put this in dlookup function to find only values, which will consist of sFilter value.
Thanks in advance!
Jun 10 '12 #1

✓ answered by nico5038

For a LIKE you need to use a "*" or "%" as wildcharacter.
So for a match with "1052-a" you would need as LIKE string "1052%" so the code would look like:
Expand|Select|Wrap|Line Numbers
  1. varTemp1 = DLookup("[paragelia]", "tbl_kataxorisi", "[paragelia] like '"& sFilter & "%'")
Nic;o)

Share this Question
Share on Google+
9 Replies


nico5038
Expert 2.5K+
P: 3,072
Change:
Expand|Select|Wrap|Line Numbers
  1. varTemp1 = DLookup("[paragelia]", "tbl_kataxorisi", "[paragelia] like 'sFilter'")
into

Expand|Select|Wrap|Line Numbers
  1. varTemp1 = DLookup("[paragelia]", "tbl_kataxorisi", "[paragelia] like '"& sFilter & "'")
Nic;o)
Jun 10 '12 #2

P: 18
thanks for your answer.
Now, i dont face any problem but i cannot have that i want because if i put in field Forms![frm_kataxorisi]![paragelia] the value 1052-a, the string sFilter take the value =1052, but after that no value in vartemp1=null, and i dont why is happening.
If i dont use like function and just take the whole field everything is ok.
Jun 10 '12 #3

nico5038
Expert 2.5K+
P: 3,072
For a LIKE you need to use a "*" or "%" as wildcharacter.
So for a match with "1052-a" you would need as LIKE string "1052%" so the code would look like:
Expand|Select|Wrap|Line Numbers
  1. varTemp1 = DLookup("[paragelia]", "tbl_kataxorisi", "[paragelia] like '"& sFilter & "%'")
Nic;o)
Jun 10 '12 #4

P: 18
thanks again.
I try this, but nothing happening.
Lets say that when i write (1052/12-a or 1234-5 or 1052-f), i take as sFilter= 1052/12 or 1234 or 1052 and i want to search my table.[paragelia], so as to see if there is a value which will consist of these values.
In my table i have values 1052/12-a-b, 1052-b, 1234.
Jun 10 '12 #5

nico5038
Expert 2.5K+
P: 3,072
Can you place a breakpoint in the code (click in left ruler) and execute the code till the DLOOKUP.
Next type in the immediate window at the bottom:
Expand|Select|Wrap|Line Numbers
  1. ?"[paragelia] like '"& sFilter & "%'"
and report here the result.

Nic;o)
Jun 10 '12 #6

P: 18
here is my report
[paragelia] like '1052%'
Jun 10 '12 #7

P: 18
I understand my fault nico5038, after your propositions.
I had to change my .....If varTemp = Forms![frm_kataxorisi]![ypiresia] And varTemp1 = Forms![frm_kataxorisi]![paragelia] Then...... with..... If varTemp = Forms![frm_kataxorisi]![ypiresia] And varTemp1>0 Then....., so as my code says that there is a record which catch these criterias.
Thanks very match for your replies.
P.S. Do you know any way to have management system about my ms access db, i want to create manually login users with permissions and i id like to have a log files.
Jun 10 '12 #8

nico5038
Expert 2.5K+
P: 3,072
I personally use regularly the windows userid of the person logged in. You can get that using the environ() function like:
Expand|Select|Wrap|Line Numbers
  1. strUsername = environ("username")
Thus I'm sure only people allowed to use the system will be able to run my access application. Placing the allowed users within the company in an authorization table will limit the access to the intended users and you can log the users starting the application.

Nic;o)
Jun 10 '12 #9

P: 18
thanks again.
Have a nice day!
Jun 11 '12 #10

Post your reply

Sign in to post your reply or Sign up for a free account.