dcount()

[spock1971]

I've looked at numerous threads on this site and still can't get a dcount to work.

I want the database to check if there is a valid reference number entered before opening a form.

There is a table called 'staff' with a 'payroll number' field in it. This table contains all staff.

I then want the user to enter a payroll number and retrieve the corresponding record. However, if there is no match then the user has entered the number incorrectly.

I've done:

int2=dcount("[payroll number]","staff",forms!control,payroll) and then an:

if int2=0 then msgbox
end if
exit sub

However, I either now get a message when the number is correct, or it's exitting the sub every time.

Any corrections please?

 

[Rod D]

Hi Spock
I would suggest that you change your formula from...

int2=dcount("[payroll number]","staff",forms!control,payroll)

to

int2=dcount("[payroll number]","staff","[payroll number]=" & forms!control![payroll])

I assume control! is your form name - if not, change it to be the form name. Remember that if the form name has spaces in it, it will have to be enclosed within [ ]'s.

I also assume that the payroll number's data type is numeric - not text. If it is text, then the formula should be changed to :

int2=dcount("[payroll number]","staff","[payroll number]='" & forms!control![payroll] & "'")

To put quotes around the variable forms!control![payroll]

HTH
Rod

 

[spock1971]

Thanks - will give that a go. Everybody else on this forum sems to be told to use dcount and reply that it works.

Must be me.