SQL problem

[ruthib4]

Hello All,

I need your help, i have the following code:

Private Sub Summary()

Dim Rst As ADODB.Recordset
Dim strQuery As String
Dim Temp As Variant
Dim N As Long
dim fecha2 as Date
fecha2 = Forms!Apertura!cmdFecha

Set Rst = New ADODB.Recordset
strQuery = "SELECT [Itaca Cards].País, Sum(TCpaises![Cambio Medio]*[Itaca Cards]![Real OK]/1000000), " & _
"Sum(TCpaises![Cambio Medio]*[Itaca Cards]![Presupuestado OK]/1000000)" & _
"FROM [Itaca Cards] INNER JOIN TCpaises ON [Itaca Cards].País = TCpaises.[País Tipo de Cambio] " & _
"WHERE ((([Itaca Cards].Descripción) = 'Orex fraude' And " & _
"(([Itaca Cards].Fecha) >= #1/1/2011# And ([Itaca Cards].Fecha) <= " & fecha2 & ") " & _
"And (([Itaca Cards].Negocio) = 'ecr' Or ([Itaca Cards].Negocio) = 'edb'))) GROUP BY [Itaca Cards].País;"

Rst.Open strQuery, CurrentProject.Connection, adOpenStatic, adLockOptimistic
Temp = Rst.GetRows

With Me
.ar1 = Temp(0, N)
.ar2 = Temp(1, N)
.ar3 = Temp(2, N)
.ar4 = Temp(3, N)
.ar5 = Temp(4, N)
.ar6 = Temp(5, N)


.ar7 = Temp(6, N)
End With
Rst.Close
Set Rst = Nothing

End Sub

I am getting an error '3021'. "Either BOF or EOF is true, or the current record has been deleted. Requested operation requires a record"

What is wrong here?

Thanks in advance

[Toyman]

Hello All,

I need your help, i have the following code:

Private Sub Summary()

Dim Rst As ADODB.Recordset
Dim strQuery As String
Dim Temp As Variant
Dim N As Long
dim fecha2 as Date
fecha2 = Forms!Apertura!cmdFecha

Set Rst = New ADODB.Recordset
strQuery = "SELECT [Itaca Cards].País, Sum(TCpaises![Cambio Medio]*[Itaca Cards]![Real OK]/1000000), " & _
"Sum(TCpaises![Cambio Medio]*[Itaca Cards]![Presupuestado OK]/1000000)" & _
"FROM [Itaca Cards] INNER JOIN TCpaises ON [Itaca Cards].País = TCpaises.[País Tipo de Cambio] " & _
"WHERE ((([Itaca Cards].Descripción) = 'Orex fraude' And " & _
"(([Itaca Cards].Fecha) >= #1/1/2011# And ([Itaca Cards].Fecha) <= " & fecha2 & ") " & _
"And (([Itaca Cards].Negocio) = 'ecr' Or ([Itaca Cards].Negocio) = 'edb'))) GROUP BY [Itaca Cards].País;"

Rst.Open strQuery, CurrentProject.Connection, adOpenStatic, adLockOptimistic
Temp = Rst.GetRows

With Me
.ar1 = Temp(0, N)
.ar2 = Temp(1, N)
.ar3 = Temp(2, N)
.ar4 = Temp(3, N)
.ar5 = Temp(4, N)
.ar6 = Temp(5, N)
.ar7 = Temp(6, N)
End With
Rst.Close
Set Rst = Nothing

End Sub

I am getting an error '3021'. "Either BOF or EOF is true, or the current record has been deleted. Requested operation requires a record"

What is wrong here?

Thanks in advance

([Itaca Cards].Fecha) <= #" & fecha2 & "#)"

You need to put the "#" around the date value to defind it as a date value

[ruthib4]

Thanks a lot, i solved it, however i have antoher issue, as you can see in the SQL statement i am usin SUM to accumulate the values of 12 months for each of the fields but we i run the code it gives me only the first month values, it is not adding the rest of the months.

Is there any wrong with my code?

I built the same sql in a query in access and it gives me the correct values, here is it.

SELECT Sum(TCpaises![Cambio Medio]*[Itaca Cards]![Real OK]/1000000) AS [Real OK TC], Sum(TCpaises![Cambio Medio]*[Itaca Cards]![Presupuestado OK]/1000000) AS [Presup OK TC]
FROM [Itaca Cards] INNER JOIN TCpaises ON [Itaca Cards].País = TCpaises.[País Tipo de Cambio]
WHERE ((([Itaca Cards].Descripción)="orex fraude") AND (([Itaca Cards].Fecha)>=#1/1/2011# And ([Itaca Cards].Fecha)<=[Formularios]![Apertura]![cmdFecha]) AND (([Itaca Cards].Negocio)="ecr" Or ([Itaca Cards].Negocio)="edb"))
GROUP BY [Itaca Cards].País;

Thanks again

[Toyman]

Thanks a lot, i solved it, however i have antoher issue, as you can see in the SQL statement i am usin SUM to accumulate the values of 12 months for each of the fields but we i run the code it gives me only the first month values, it is not adding the rest of the months.

Is there any wrong with my code?

I built the same sql in a query in access and it gives me the correct values, here is it.

SELECT Sum(TCpaises![Cambio Medio]*[Itaca Cards]![Real OK]/1000000) AS [Real OK TC], Sum(TCpaises![Cambio Medio]*[Itaca Cards]![Presupuestado OK]/1000000) AS [Presup OK TC]
FROM [Itaca Cards] INNER JOIN TCpaises ON [Itaca Cards].País = TCpaises.[País Tipo de Cambio]
WHERE ((([Itaca Cards].Descripción)="orex fraude") AND (([Itaca Cards].Fecha)>=#1/1/2011# And ([Itaca Cards].Fecha)<=[Formularios]![Apertura]![cmdFecha]) AND (([Itaca Cards].Negocio)="ecr" Or ([Itaca Cards].Negocio)="edb"))
GROUP BY [Itaca Cards].País;

Thanks again

Have you check to see whether the criteria you set does indeed return values for all months?

[ruthib4]

I wrote an app in access VBA and runs perfectly here in Spain (date format dd/mm/yyy), went to Poland and try to run it there but gave problems with the date format yyyy/mm/dd, is there any way using vba to avoid this?.

Regards