Validation rule error should not appear ? (1 Viewer)

dcavaiani

Registered User.
Local time
Today, 04:55
Joined
May 26, 2014
Messages
385
Code:
strSQL = "insert into rates ([customer], [ratecode], [saturdaystart], [fridayend], [rate]) values (""" & [findcust] & """, 'L', " & [LastSaturday] & ", " & [LastFriday] & ", " & Me.RateperHour & ")"
CurrentDb.Execute strSQL, dbFailOnError

On debugging, I see that value of [LastFriday] is definitely greater than the value of [LastSaturday]. However, this table validation rule error occurs: [fridayend]>[saturdaystart], and Msg = End Date must be Greater than Start Date
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:55
Joined
Oct 29, 2018
Messages
21,358
Hi. Are those fields date/time or short text data types?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:55
Joined
May 7, 2009
Messages
19,175
use beforeUpdate event of each tbox to validate your entry.
 

dcavaiani

Registered User.
Local time
Today, 04:55
Joined
May 26, 2014
Messages
385
print lastsaturday
5/16/2020
print lastfriday
12/31/9999

run time error 3316
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:55
Joined
May 7, 2009
Messages
19,175
lastfriday is Smaller than lastsaturday, so validation is correct.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:55
Joined
May 7, 2009
Messages
19,175
becoz you define the Datatype of those fields as Short Text not As Date/Time.

use Format([lastfriday], "\#mm\/dd\/yyyy\#") on your Insert query
 

dcavaiani

Registered User.
Local time
Today, 04:55
Joined
May 26, 2014
Messages
385
becoz you define the Datatype of those fields as Short Text not As Date/Time.

use Format([lastfriday], "\#mm\/dd\/yyyy\#") on your Insert query
They are defined Date/Time with Format = short date
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:55
Joined
May 7, 2009
Messages
19,175
if it is a Variable you must Delimit them with # and in yyyy-mm-dd format or US-EN format for date.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:55
Joined
May 7, 2009
Messages
19,175
Code:
strSQL = "insert into rates ([customer], [ratecode], [saturdaystart], [fridayend], [rate]) values (""" & [findcust] & """, 'L', " & Format([LastSaturday], "\#mm\/dd\/yyyy\#") & ", " & Format([LastFriday]"\#mm\/dd\/yyyy\#") & ", " & Me.RateperHour & ")"
 

dcavaiani

Registered User.
Local time
Today, 04:55
Joined
May 26, 2014
Messages
385
Code:
strSQL = "insert into rates ([customer], [ratecode], [saturdaystart], [fridayend], [rate]) values (""" & [findcust] & """, 'L', " & Format([LastSaturday], "\#mm\/dd\/yyyy\#") & ", " & Format([LastFriday]"\#mm\/dd\/yyyy\#") & ", " & Me.RateperHour & ")"
That makes sense but must be a slight error in that code ? It's in Red.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:55
Joined
May 7, 2009
Messages
19,175
Code:
Dim db As DAO.Database
Set db = CurrentDb
With db.CreateQueryDef("", _
       "insert into rates ([customer], [ratecode], [saturdaystart], [fridayend], [rate]) values (" & _
        "p0, p1, p2, p3, p4);")
        .Parameters(0) = [findcust]
        .Parameters(1) = "L"
        .Parameters(2) = [LastSaturday]
        .Parameters(3) = [LastFriday]
        .Parameters(4) = [RateperHour]
        
        .Execute (dbFailOnError)
End With
Set db = Nothing
 

dcavaiani

Registered User.
Local time
Today, 04:55
Joined
May 26, 2014
Messages
385
Code:
Dim db As DAO.Database
Set db = CurrentDb
With db.CreateQueryDef("", _
       "insert into rates ([customer], [ratecode], [saturdaystart], [fridayend], [rate]) values (" & _
        "p0, p1, p2, p3, p4);")
        .Parameters(0) = [findcust]
        .Parameters(1) = "L"
        .Parameters(2) = [LastSaturday]
        .Parameters(3) = [LastFriday]
        .Parameters(4) = [RateperHour]
       
        .Execute (dbFailOnError)
End With
Set db = Nothing
Worked perfectly !!!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:55
Joined
May 7, 2009
Messages
19,175
it is better to use the Querydef, you don't need to worry about the Delimiters.
just plug and play.
 

Users who are viewing this thread

Top Bottom