annoying problem!

[krys]

Hi!

I'm having a 'type mismatch' error in the following SQL statement in my VB code...

stSQL = "Select [AmountPerHour] from PayRates where [Pay_Rate_ID] = 'marking' And [Rate_Date] = Me![Rate_Date] "

After debugging I can see that Rate_Date holds the value 9/09/2004 as does Me![Rate_Date] but it still has this error...

Does anyone have any idea what might be the problem? I tried writing this in SQL view of a query and it seems to work only when I do Rate_Date with a value of 9/09/04 instead of 2004 although the table has it in the above form (2004)...

Thank you!
Krys

 

[Mile-O]

stSQL = "Select [AmountPerHour] from PayRates where [Pay_Rate_ID] = 'marking' And [Rate_Date] = Me![Rate_Date] "

Dates, as criteria, use the # delimiter.

stSQL = "SELECT [AmountPerHour] FROM PayRates " & _
    "WHERE [Pay_Rate_ID] = ""marking"" AND [Rate_Date] = #" & Me.[Rate_Date] & "#"

 

[krys]

just one more problem with that

Thank you very much for your help...

It seems to work for the first one.. then I get a:

'Run-time error '3021':

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.'

Below is the code:
***************************************************
Private Sub PrepHours_AfterUpdate()
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim stSQL As String

Set con = CurrentProject.Connection

stSQL = "SELECT [AmountPerHour] FROM PayRates " & _
"WHERE [Pay_Rate_ID] = ""A02"" AND [Rate_Date] = #" & Me.[Rate_Date] & "#"

rs.Open stSQL, con, adOpenKeyset ', adLockReadOnly
rs.MoveFirst
Me![PrepPay] = rs![AmountPerHour] * Me![PrepHours]

End Sub
***************************************************

Thank you for your help!!!
Krys

 

[jgc31]

Krys

Are there any records being returned by your query?

In debug mode check the RecordCount property of the recordset in the Locals window.

 

[Mile-O]

Private Sub PrepHours_AfterUpdate()
 Dim con As New ADODB.Connection
 Dim rs As New ADODB.Recordset
 Dim stSQL As String

 Set con = CurrentProject.Connection

 stSQL = "SELECT [AmountPerHour] FROM PayRates " & _
    "WHERE [Pay_Rate_ID] = ""A02"" AND [Rate_Date] = #" & Me.[Rate_Date] & "#"

rs.Open stSQL, con, adOpenKeyset ', adLockReadOnly
rs.MoveFirst
Me![PrepPay] = rs![AmountPerHour] * Me![PrepHours]

End Sub

Private Sub PrepHours_AfterUpdate()
 Dim con As New ADODB.Connection
 Dim rs As New ADODB.Recordset
 Dim stSQL As String

 Set con = CurrentProject.Connection

 stSQL = "SELECT [AmountPerHour] FROM PayRates " & _
    "WHERE [Pay_Rate_ID] = ""A02"" AND [Rate_Date] = #" & Me.[Rate_Date] & "#"

rs.Open stSQL, con, adOpenKeyset ', adLockReadOnly
    If Not rs.BOF And Not rs.EOF Then        
        rs.MoveFirst
        Me.[PrepPay] = rs![AmountPerHour] * Me.[PrepHours]
    End If

End Sub

 

[krys]

still problem

Thank you both for your replies!

The problem is that it is finding BOF and EOF when it shouldn't .

So - if I select a pay rate of 'marking' with a date '9/09/2004', it seems to work and find the correct amount of pay... if I ever select a pay rate of 'marking' with a date of '1/08/2004', it finds nothing (BOF/EOF prob). (The pay rate for this will always be marking - just a different date depending on when a person gets paid.. Do I have it in the wrong event? I have it on 'after_update'...

Thank you! I'm sure its something so small but it just wont go away!

Krys

 

[WayneRyan]

krys,

First, try to get the value for A02. If there is none, the Nz function will provide a
0. If that's the case look for it with ""marking"".

Dim AmountPerHour As Currency

AmountPerHour = Nz(DLookUp("[AmountPerHour]", _
                           "PayRates",        _
                           "[Pay_Rate_ID] = ""A02"" AND [Rate_Date] = #" & Me.[Rate_Date] & "#"), 0)
If AmountPerHour = 0 Then
   AmountPerHour = Nz(DLookUp("[AmountPerHour]", _
                              "PayRates",        _
                              "[Pay_Rate_ID] = ""marking"" AND [Rate_Date] = #" & Me.[Rate_Date] & "#"), 0)
End If

Wayne

 

[krys]

day/month swap

Thank you for that!

I think I may have found the real problem. In the rate_date field, I store the values '1/8/2004' as in 1st August. When I write a simple select query like...

select pay_rate, rate_date from PayRates
where pay_rate = 'marking' and
rate_date = #1/8/04#; -> this doesn't work... if I put in #8/1/04# it will work. This seems to be the problem why its not finding this date in the VB code...

Is there any way I can force it to look it up as 1/8/04? (in VB)?

Thank you!
Krys

 

[jgc31]

Krys

VBA should be aware of the regional sttings as specified in the users control panel, regional settings applet. Dates are stored by VBA as a floating point number where the integer represents the day and the decimal part the time (as a percentage). I believe that day 0 was Dec 30th 1899. So VBA will translate #1/8/04# to a number whose value will depend on those regional settings.

 

[jgc31]

Krys

Just checked the access help file and it suggests that in VBA SQL statements you must use US format dates although regional settings will work fine on the design grid.

 

[krys]

Thank you... I was thinking that could be the case... what a punish!