Dcount Type Mismatch

[cwh060]

Hi there, I’m not great as a developer, but I have a small timesheet
application developed in Access (2000). I am trying to use Dcount in the
Before Update to warn a user if they have input a duplicate timesheet. The
tbl_Main is quite simple, it has three columns. A primary key, Text input
field and Date/Time input field.

The code works if I use either the text field (Chris) or the Week Ending Date
field (14-Nov-08) but not together using an AND expression. I know it’s
probably because I am comparing a text field to a date field but for the life
of me, I can’t figure this out.

I have searched through the forum for answers, I find a variety of questions,
but nothing seems to overcome my simple error of “Type Mismatchâ€.

Any help would be appreciated. I've tried all sorts of different format
combinations on the date field but nothing has worked so far.


Thanks
cwh060

tbl_Main looks like this.

RecordID Staff Member Week Ending Date
100 Chris 14-Nov-08

My code looks like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)


sName = "[Staff Member]= '" & Me.Combo4 & "' "
sWeek = Format(Me.[Combo6], ("dd\/mm\/yy" & ""))

If DCount("*", "tbl_Main", sName And sWeek) > 0 Then

MsgBox "This is a duplicate Record", vbOKCancel
Me.Undo
Else
End If

End Sub

 

[Beetle]

A couple of things stand out right off.

First, your sWeek variable is only formatting the value from your combo box,
not comparing it to a field value.

Second, the And needs to be inside quotes. So, modified, it might look like;


Private Sub Form_BeforeUpdate(Cancel As Integer)

sName = "[Staff Member]= """ & Me.Combo4 & """"
sWeek = "[Week Ending Date]=#" _
& Format(Me.[Combo6], ("dd\/mm\/yy" & "")) & "#""

If DCount("*", "tbl_Main", sName & " And " & sWeek) > 0 Then

MsgBox "This is a duplicate Record", vbOKCancel
Me.Undo
Else
End If

End Sub


I have modified the quotes in the sName string to allow for the possibility
of an apostrophe in the Staff Member name.

I'm still not sure it's going to return a match depending on what values
are in your date combo box (Combo6), and the formatting, etc.

On a side note, if you have a table of staff members (which you should)
then you should only be storing the StaffID in this table, not the name.
Also, you should give your controls more meaningful names. Names like
Combo4 and Combo6 tell you absolutely nothing about the purpose
of the control, so when you need to revisit this application in six months,
you could be pulling your hair out trying to make sense of your code.

 

[cwh060]

Thanks, agreed I should be changing the Combo's to meaning full names.

The Value in the table where Combo6 is taking the information is a Date/Time
value, Fomatted as a Medium Date (14-Nov-08). It always must represent the
last Friday of any given week. The result from the users selection is stored
in the tbl_Main as the same.

I've tried the code you suggested and I'm getting:

Run-time error '3075':
Syntax error in string in query expression '[Staff Member] = "Chris" And
[Week Ending Date] =#14/11/08#'"

Appreciate the response.
cwh

A couple of things stand out right off.

First, your sWeek variable is only formatting the value from your combo box,
not comparing it to a field value.

Second, the And needs to be inside quotes. So, modified, it might look like;

Private Sub Form_BeforeUpdate(Cancel As Integer)

sName = "[Staff Member]= """ & Me.Combo4 & """"
sWeek = "[Week Ending Date]=#" _
& Format(Me.[Combo6], ("dd\/mm\/yy" & "")) & "#""

If DCount("*", "tbl_Main", sName & " And " & sWeek) > 0 Then

MsgBox "This is a duplicate Record", vbOKCancel
Me.Undo
Else
End If

End Sub

I have modified the quotes in the sName string to allow for the possibility
of an apostrophe in the Staff Member name.

I'm still not sure it's going to return a match depending on what values
are in your date combo box (Combo6), and the formatting, etc.

On a side note, if you have a table of staff members (which you should)
then you should only be storing the StaffID in this table, not the name.
Also, you should give your controls more meaningful names. Names like
Combo4 and Combo6 tell you absolutely nothing about the purpose
of the control, so when you need to revisit this application in six months,
you could be pulling your hair out trying to make sense of your code.

Hi there, I’m not great as a developer, but I have a small timesheet
application developed in Access (2000). I am trying to use Dcount in the

[quoted text clipped - 36 lines]

End Sub

 

[Mike Painter]

Thanks, agreed I should be changing the Combo's to meaning full names.

The Value in the table where Combo6 is taking the information is a
Date/Time value, Fomatted as a Medium Date (14-Nov-08). It always
must represent the last Friday of any given week. The result from the
users selection is stored in the tbl_Main as the same.

I've tried the code you suggested and I'm getting:

Run-time error '3075':
Syntax error in string in query expression '[Staff Member] = "Chris"
And [Week Ending Date] =#14/11/08#'"

The single quote at the beginning matches the one at the end so there is an
extra quote sing.

 

[cwh060 via AccessMonster.com]

Not sure I understand.

The code looks like this:

sName = "[Staff Member]= """ & Me.Combo4 & """"
sWeek = "[Week Ending Date]=#" _
& Format(Me.[Combo6], ("dd\/mm\/yy" & "")) & "#"""

I see the error text on the message box is putting an extra ' in the message;
however it's not evident anywhere in the code. If I try to take out the extra
quotation " at the end of the statements, the Code builder keeps putting it
back in?

I have manged to get this statement to sort of work.

If DCount("*", "tbl_Main", "[tbl_Main]![Staff Member]" & " And " & "[tbl_Main]
![Week Ending Date]") > 0 Then

But now it's giving me the message on every new record I create.

Thanks, agreed I should be changing the Combo's to meaning full names.

[quoted text clipped - 8 lines]

Syntax error in string in query expression '[Staff Member] = "Chris"
And [Week Ending Date] =#14/11/08#'"

The single quote at the beginning matches the one at the end so there is an
extra quote sing.

 

[Douglas J. Steele]

Regardless of your regional settings, you cannot use dd/mm/yyyy in a domain
aggregate function. You must use mm/dd/yyyy or a unambiguous format like
yyyy-mm-dd or dd mmm yyyy. (Yes, I know mm/dd/yyyy is ambiguous, but Access
was developed in the US...)

14/11/08 will be correctly interpretted as 14 Nov, 2008, but 12/11/08 will
ALWAYS be interpretted as 11 Dec, 2008

Try:

sName = "[Staff Member]= """ & Me.Combo4 & """"
sWeek = "[Week Ending Date]=" _
& Format(Me.[Combo6], ("\#yyyy\-mm\-dd\#")


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Not sure I understand.

The code looks like this:

sName = "[Staff Member]= """ & Me.Combo4 & """"
sWeek = "[Week Ending Date]=#" _
& Format(Me.[Combo6], ("dd\/mm\/yy" & "")) & "#"""

I see the error text on the message box is putting an extra ' in the
message;
however it's not evident anywhere in the code. If I try to take out the
extra
quotation " at the end of the statements, the Code builder keeps putting
it
back in?

I have manged to get this statement to sort of work.

If DCount("*", "tbl_Main", "[tbl_Main]![Staff Member]" & " And " &
"[tbl_Main]
![Week Ending Date]") > 0 Then

But now it's giving me the message on every new record I create.

Thanks, agreed I should be changing the Combo's to meaning full names.

[quoted text clipped - 8 lines]

Syntax error in string in query expression '[Staff Member] = "Chris"
And [Week Ending Date] =#14/11/08#'"

The single quote at the beginning matches the one at the end so there is
an
extra quote sing.

 

[Mike Painter]

I have manged to get this statement to sort of work.

If DCount("*", "tbl_Main", "[tbl_Main]![Staff Member]" & " And " &
"[tbl_Main] ![Week Ending Date]") > 0 Then

But now it's giving me the message on every new record I create.

If DCount("*", "tbl_Main", "[tbl_Main]![Staff Member]" & " And " &

"[tbl_Main] ![Week Ending Date]") > 0 Then

"[tbl_Main]![Staff Member] And " & "[tbl_Main] ![Week Ending Date]" is the
criteria
and would return a string that equals
"[tbl_Main]![Staff Member] And [tbl_Main] ![Week Ending Date]"

[Staff Member] and [Week Ending Date] are fields in your domain but
tbl_Main![staff member], etc are not.

something like "[Staff Member] = " & Me![staff member] would give all the
Smiths if me!staffmember was "Smith"

 

[cwh060 via AccessMonster.com]

Douglas,

This worked perfectly!

sWeek = "[Week Ending Date]=" _
& Format(Me.[Combo6], ("\#yyyy\-mm\-dd\#"))

I missed the closing ) the first time around.

Excellent Forum, thankyou.
chris

Regardless of your regional settings, you cannot use dd/mm/yyyy in a domain
aggregate function. You must use mm/dd/yyyy or a unambiguous format like
yyyy-mm-dd or dd mmm yyyy. (Yes, I know mm/dd/yyyy is ambiguous, but Access
was developed in the US...)

14/11/08 will be correctly interpretted as 14 Nov, 2008, but 12/11/08 will
ALWAYS be interpretted as 11 Dec, 2008

Try:

sName = "[Staff Member]= """ & Me.Combo4 & """"
sWeek = "[Week Ending Date]=" _
& Format(Me.[Combo6], ("\#yyyy\-mm\-dd\#")

Not sure I understand.

[quoted text clipped - 29 lines]