Need Help Executing a Query in VB in Access

[StuckfromPR]

I need help urgently on executing a query which calculates a percentage (when a button is clicked in a form) , and I need the value of that percentage to be posted in the same form. This is the code that I have so far, please help me out since I am STUCK.

Private Sub Sub_Btn_Click()
Dim db As Connection
Dim rs As Recordset
Dim strId As String
Dim strMonth As String
Dim strWeek As String
Dim strSQL As String
Set db = CurrentProject.Connection
Set rs = adodb.Recordset
txtValue.Value = "" 'This is where I need the value to go into.
strId = EID_drp.Value
strMonth = Month_drp.Value
strWeek = Week_drp.Value

strSQL = "SELECT Sum([Table1].[Hours])/40*100 AS txtValue " & _
"FROM [Table1] INNER JOIN [Table2] ON [Table1].[ChargeDate] = [Table2].[CalendarDay] " & _
"WHERE Table1.EmpIdNbr= '" & strId & "' AND Table2.CalendarWeek= '" & strWeek & "'" & _
" AND Table2.CalendarMonth= '" & strMonth & "' AND ((Table1.Charge) Like '7%';"

rs.OpenRecordset (strSQL)

End Sub

This is not working for me, please HELP!!!!!!
Thanks!

 

[MSAccessRookie]

I need help urgently on executing a query which calculates a percentage (when a button is clicked in a form) , and I need the value of that percentage to be posted in the same form. This is the code that I have so far, please help me out since I am STUCK.

Private Sub Sub_Btn_Click()
Dim db As Connection
Dim rs As Recordset
Dim strId As String
Dim strMonth As String
Dim strWeek As String
Dim strSQL As String
Set db = CurrentProject.Connection
Set rs = adodb.Recordset
txtValue.Value = "" 'This is where I need the value to go into.
strId = EID_drp.Value
strMonth = Month_drp.Value
strWeek = Week_drp.Value

strSQL = "SELECT Sum([Table1].[Hours])/40*100 AS txtValue " & _
"FROM [Table1] INNER JOIN [Table2] ON [Table1].[ChargeDate] = [Table2].[CalendarDay] " & _
"WHERE Table1.EmpIdNbr= '" & strId & "' AND Table2.CalendarWeek= '" & strWeek & "'" & _
" AND Table2.CalendarMonth= '" & strMonth & "' AND ((Table1.Charge) Like '7%';"

rs.OpenRecordset (strSQL)

End Sub

This is not working for me, please HELP!!!!!!
Thanks!

I tested the statement and the value of strSQL (modified for ease of readability) that I created was:

[B][SIZE=3][COLOR=black][FONT=Courier New]SELECT Sum([Table1].[Hours])/40*100 AS txtValue [/FONT][/COLOR][/SIZE][/B]
[B][SIZE=3][COLOR=black][FONT=Courier New]FROM [Table1] INNER JOIN [Table2] [/FONT][/COLOR][/SIZE][/B]
[B][SIZE=3][COLOR=black][FONT=Courier New]ON [Table1].[ChargeDate] = [Table2].[CalendarDay] " [/FONT][/COLOR][/SIZE][/B]
[B][SIZE=3][COLOR=black][FONT=Courier New]WHERE Table1.EmpIdNbr=[SIZE=5][COLOR=red] ''[/COLOR][/SIZE] AND Table2.CalendarWeek=[SIZE=5][COLOR=red] ''[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/B]
[B][SIZE=3][COLOR=black][FONT=Courier New]AND Table2.CalendarMonth=[SIZE=5][COLOR=red] ''[/COLOR][/SIZE] AND ((Table1.Charge) Like '7%';[/FONT][/COLOR][/SIZE][/B]

It would appear that the values of strId, strWeek, " AND strMonth are not being translated properly

 

[StuckfromPR]

The problem is not in that part of the query, I made a test of having a pop up display the string and the values are posting correctly. My three problems are:

1. The like section of the query,
AND ((Table1.Charge) Like '7%';

2. Executing the query.

3. Having the results of the query in the value of the text box. txtValue

I know it has to be something simple, and I know I have done this before, but I just can't remember how to do it.

 

[gemma-the-husky]

AND ((Table1.Charge) Like '7%';

is table1.charge a string, or a number

if a number then like 0.07, probably

 

[StuckfromPR]

Table1.Charge is a string, the % is the Wild card, I want to search for everything that begins with 7

 

[MSAccessRookie]

Table1.Charge is a string, the % is the Wild card, I want to search for everything that begins with 7

You were correct about my errant comment from before. I failed to define the items. Defining them showed the correct values.

You also said that the character "%" was your wildcard. Isn't the wildcard for MS Access a "*"? I think you are using Oracle or MS SQL Server format by mistake. Try substituting a "*".

 

[StuckfromPR]

I changed it to this, but is giving me this error now.
Run-time error '-2147217913 (80040e07)':
Data type mismatch in criteria expression.

Private Sub Sub_Btn_Click()
Dim con As Object
Dim rs As Object
Dim stSql As String
Dim strId As String
Dim strMonth As String
Dim strWeek As String
strId = EID_drp.Value
strMonth = Month_drp.Value
strWeek = Week_drp.Value
Set con = Application.CurrentProject.Connection
stSql = "SELECT Sum([Table1].[Hours])/40*100 AS MySumValue " & _
"FROM [Table1] INNER JOIN [Table2] ON [Table1].[ChargeDate] = [Table2].[CalendarDay] " & _
"WHERE Table1.EmpIdNbr= '" & strId & "' AND Table2.CalendarWeek= '" & strWeek & "'" & _
" AND Table2.CalendarMonth= '" & strMonth & "' AND (((Table1.[Charge]) Like '7*'));"

Set rs = CreateObject("ADODB.Recordset")
rs.Open stSql, con, 1 ' 1 = adOpenKeyset

If (rs.EOF) Then
'There is nothing to sum on the table
Else
While (Not (rs.EOF))
txtBox.Value = rs![MySumValue]
rs.MoveNext
Wend
End If
' Close the recordset and the database.
rs.Close
Set rs = Nothing
Set con = Nothing
End Sub


I changed it to this, but is giving me this error now.
Run-time error '-2147217913 (80040e07)':
Data type mismatch in criteria expression.

 

[MSAccessRookie]

I changed it to this, but is giving me this error now.
Run-time error '-2147217913 (80040e07)':
Data type mismatch in criteria expression.

Private Sub Sub_Btn_Click()
Dim con As Object
Dim rs As Object
Dim stSql As String
Dim strId As String
Dim strMonth As String
Dim strWeek As String
strId = EID_drp.Value
strMonth = Month_drp.Value
strWeek = Week_drp.Value
Set con = Application.CurrentProject.Connection
stSql = "SELECT Sum([Table1].[Hours])/40*100 AS MySumValue " & _
"FROM [Table1] INNER JOIN [Table2] ON [Table1].[ChargeDate] = [Table2].[CalendarDay] " & _
"WHERE Table1.EmpIdNbr= '" & strId & "' AND Table2.CalendarWeek= '" & strWeek & "'" & _
" AND Table2.CalendarMonth= '" & strMonth & "' AND (((Table1.[Charge]) Like '7*'));"

Set rs = CreateObject("ADODB.Recordset")
rs.Open stSql, con, 1 ' 1 = adOpenKeyset

If (rs.EOF) Then
'There is nothing to sum on the table
Else
While (Not (rs.EOF))
txtBox.Value = rs![MySumValue]
rs.MoveNext
Wend
End If
' Close the recordset and the database.
rs.Close
Set rs = Nothing
Set con = Nothing
End Sub


I changed it to this, but is giving me this error now.
Run-time error '-2147217913 (80040e07)':
Data type mismatch in criteria expression.

I have highlighted what I believe is the criteria expression in red. You As best as I can determine it interprets to the following:

[B][COLOR=black]WHERE Table1.EmpIdNbr={ Value of strId } AND[/COLOR][/B]
[COLOR=black][B]Table2.CalendarWeek={ Value of strWeek } [/B][B]AND[/B][/COLOR]
[B][COLOR=black]Table2.CalendarMonth={ Value of strMonth } AND [/COLOR][/B]
[B][COLOR=black](((Table1.[Charge]) Like '7*'));[/COLOR][/B]

The most common reason for the error is a String/Number mixup. You will need to verify the following:

1. Table1.EmpIdNbr is the same DataType as strId
2. Table2.CalendarWeek is the same DataType as strWeek
3. Table2.CalendarMonth is the same DataType as strMonth

Most likely, one or more of these three has an integer on one side and a string on the other. Standard conversion functions should repair the problem.

 

[StuckfromPR]

Thanks so much, that was it, I had a number in one of those!!

 

[MSAccessRookie]

Thanks so much, that was it, I had a number in one of those!!

Glad to be of assistance. Don't feel too bad about it, this type of problem still occurs for me from time to time.