dcount + intr

[Jackske]

About the DCount function.
Dcount (“[Field]”, “Table”, “criteria”)

I am calling this function from a form while I am adding a record.
I use the criteria argument to compare if the ‘Name’ in my table is = ‘Name’ in my form.
The syntax of the criteria is “[Name]=[forms]![frmMembers].[Form]![Name]”
It works perfectly as long that both as identical.

Now I would like to change the code in order to check if the first 3 letters of Name (in the table) appears in Name (in the form).
I did try to use Instr without any success.

Thanks for any help

 

[Mile-O]

Dcount ("[Field]", "Table", "Left([Name],3)=Left([forms]![frmMembers].[Form]![Name],3)")

 

[Jackske]

Thanks but with your code the 3 first have to be identical to the fother first 3.
I would like to contre if "cde" appears in "abcdef"
Thanks,
Jacques-Belgium

 

[simongallop]

If "cde" is left(Name,3) then:

Dcount ("[Field]", "Table", "Instr(([forms]![frmMembers].[Form]![Name]),left([Name],3))")

HTH

 

[Jackske]

you did omit Name=... in the criteria.
Your code will not work.
Any way, thanks

 

[simongallop]

My code will not work because I copied it from earlier in the post and din't bother to check the form reference.

Dcount ("[Nuf]", "DCTest", "Instr(Forms.frmDCT.txtSearch,left([User],3))")

The above code works perfectly well in a test form that I built. Just so that you can recreate it:

Create table with 2 fields: Nuf and User. Call table DCTest
Create an unbound form with a textbox, a label and a button. Call the textbox txtSearch, the label lblResult and the form frmDCT. For the OnClick event of the button stick the following code:

Me.lblResult.Caption = Dcount ("[Nuf]", "DCTest", "Instr(Forms.frmDCT.txtSearch,left([User],3))")

Now add some data to the table that you have created and the code will work.

 

[Jackske]

In Antwerp we say 'Dank U'.