Dcount Help (1 Viewer)

B

bleep-blop

Registered User.

Local time

Today, 21:30

Joined

Sep 30, 2013

Messages

24

• Sep 30, 2013

• #1

Hi,

Trying to get a Dcount to work. My code so far (not working)

Code:Copy to clipboard

DCount("OracleID", "TblEmpAb", "(OracleID = '" & txtOracleID & "'" & " AND AbStart =#" & Me.txtAbStart & "#")

Im using this VBA to prevent a new absence case being created if the employee selected already has a case open.

OracleID is a string
Abstart is a date

Been running round in circles.

Any help would be appreciated.

 

bob fitz

AWF VIP

Local time

Today, 21:30

Joined

May 23, 2011

Messages

4,726

• Sep 30, 2013

• #2

You say it does not work. Can you more specific.
Try:

Code:Copy to clipboard

DCount("OracleID", "TblEmpAb", "(OracleID = '" & txtOracleID & "' AND AbStart =#" & Me.txtAbStart & "#")

 

pr2-eugin

Super Moderator

Local time

Today, 21:30

Joined

Nov 30, 2011

Messages

8,494

• Sep 30, 2013

• #3

Hello bleep-blop, Welcome to AWF.

I think the problem you are facing would be the International Access Date problem, try formatting the Date entry..

Code:Copy to clipboard

DCount("OracleID", "TblEmpAb", "OracleID = '" & txtOracleID & "' AND AbStart = #" & Format(Me.txtAbStart, "mm\/dd\/yyyy") & "#")

Also you had an Opening parentheses before OracleID, and but did not close it.

 

D

DavidAtWork

Registered User.

Local time

Today, 21:30

Joined

Oct 25, 2011

Messages

699

• Sep 30, 2013

• #4

try

Code:Copy to clipboard

DCount("OracleID", "TblEmpAb", "[OracleID] = '" & txtOracleID & "' AND [AbStart] = #" & Me.txtAbStart & "#")

 

B

bleep-blop

Registered User.

Local time

Today, 21:30

Joined

Sep 30, 2013

Messages

24

• Sep 30, 2013

• #5

Thanks for all the responses. It was counting 0 when I knew the criteria should count 5.

PR2-eugin your code worked straight away.

Thanks all

 

pr2-eugin

Super Moderator

Local time

Today, 21:30

Joined

Nov 30, 2011

Messages

8,494

• Sep 30, 2013

• #6

Glad to help. Good Luck.

 

NigelShaw

Registered User.

Local time

Today, 21:30

Joined

Jan 11, 2008

Messages

1,573

• Sep 30, 2013

• #7

Hi

Couldn't you simply create a report to check for open cases? You could challenge that query with a VBA record set and fine tune it quite easily.

Nigel