|
|
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
|
|
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.
--
_________
Sean Bailey
"cwh060" wrote:
[Quoted Text] > 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
>
>
|
|
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
Beetle wrote:
[Quoted Text] >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
|
|
cwh060 wrote:
[Quoted Text] > 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.
|
|
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.
Mike Painter wrote:
[Quoted Text] >> 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.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200811/1
|
|
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
http://I.Am/DougSteele
(no private e-mails, please)
"cwh060 via AccessMonster.com" <u47806[ at ]uwe> wrote in message
news:8dbc1a87132be[ at ]uwe...
[Quoted Text] > 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. > > Mike Painter wrote: >>> 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. > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200811/1>
|
|
> I have manged to get this statement to sort of work.
[Quoted Text] >
> 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"
|
|
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
Douglas J. Steele wrote:
[Quoted Text] >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]
>>>an
>>>extra quote sing.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200811/1
|
|
|