Hello everybody
I am desperate,i need your help guys .I can't find a solution to the following problem!I have been searching for hours for a solution but i haven't accomplished anything.
In a Module i have the following useful code(i found it in this forum as far as i remember) which concatenates multiple records in a row based on a certain field(e.g. customer_id).
Public Function Conc(Fieldx, Identity, Value, Source) As Variant
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String
Dim vFld As Variant
Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset
vFld = Null
SQL = " SELECT DISTINCT [" & Fieldx & "] as Fld " & _
" FROM [" & Source & "] " & _
" WHERE [" & Identity & "]= ' " & Value & " ' "
' open recordset.
rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly
' concatenate the field.
Do While Not rs.EOF
If Not IsNull(rs!Fld) Then
'If InStr("vFld", "rs!Fld") = 0 Then
vFld = vFld & ", " & rs!Fld
End If
rs.MoveNext
Loop
' remove leading comma and space.
vFld = Mid(vFld, 3)
Set cnn = Nothing
Set rs = Nothing
' return concatenated string.
Conc = vFld
End Function
I have to say that I have used this code in several queries with success.
Here is the problem:
I have 3 queries ,1st query to retrieve several fields about customers ,the 2nd query that uses the 1st query and retrieves some more fields from other tables and the 3rd query in which i use the Conc function.The 3rd query retrieves data from the 2nd query.
Till here everything is ok. The conc function works great.
But if i use the Between 2 dates in the WHERE clause of a date field of the 1st query to filter records i get the following error only when i run the 2nd query.If i run only the 1st query it works,but if i run the 2nd query which uses the 1st it crashes.
RUN TIME ERROR 80040e10 - "No Value Given To One or More Required Parameters" ,when i open the debugger the th following line is highlined :
rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly
Here is the sql code of the 2nd and 3rd queries(i have ommitted the 1st query):
2nd query
SELECT 1st_query.customer_id, 1st_query.surname, 1st_query.name, 1st_query.account_number, actions.action_internal_code, activity_codes.activity_name, 1st_query.details,actions.action_date, actions.action_internal_code AS CODE
FROM 1st_query INNER JOIN (activity_codes RIGHT JOIN actions ON activity_codes.activity_code = actions.action_internal_code) ON 1st_query.account_number = actions.ypothesi_id
WHERE (((actions.action_internal_code) Not In ('002','003')) AND ((actions.action_date) Between Format([Forms]![new_actions]![txt_box_start_date],"dd/mm/yyyy") And Format([Forms]![new_actions]![txt_box_end_date],"dd/mm/yyyy")));
3rd query
SELECT 2nd_query.customer_id AS ID, 2nd_query.surname AS SURNAME, 2nd_query.name AS NAME, Conc("2nd_query.account_number","2nd_query.custome r_id",2nd_query.customer_id,"2nd_query") AS ACCOUNT, Conc("2nd_query.activity_name","2nd_query.customer _id",2nd_query.customer_id,"2nd_query") AS ACTION, 2nd_query.details AS DETAILS, 2nd_query.action_date
FROM 2nd_query;
The 2 txt boxes i use have Date/time format and i have also used the 'Between' without the Format Function but the result is the same.
I can't find why this error occurs.Probably because of the 'Between' and the date fields???
It is weird because it occurs only when i use the 'Between' sentence to filter the results of the 2nd query before running the 3rd query.If no 'Between' is used everything is ok and the 3rd query concatenates records fine.
I have to mention that all fields in all tables are text and date,no numeric.
Please Guys ,i am desperate ,i would be grateful if anyone could give me a solution.
Thank you in advance.
I am desperate,i need your help guys .I can't find a solution to the following problem!I have been searching for hours for a solution but i haven't accomplished anything.
In a Module i have the following useful code(i found it in this forum as far as i remember) which concatenates multiple records in a row based on a certain field(e.g. customer_id).
Public Function Conc(Fieldx, Identity, Value, Source) As Variant
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String
Dim vFld As Variant
Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset
vFld = Null
SQL = " SELECT DISTINCT [" & Fieldx & "] as Fld " & _
" FROM [" & Source & "] " & _
" WHERE [" & Identity & "]= ' " & Value & " ' "
' open recordset.
rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly
' concatenate the field.
Do While Not rs.EOF
If Not IsNull(rs!Fld) Then
'If InStr("vFld", "rs!Fld") = 0 Then
vFld = vFld & ", " & rs!Fld
End If
rs.MoveNext
Loop
' remove leading comma and space.
vFld = Mid(vFld, 3)
Set cnn = Nothing
Set rs = Nothing
' return concatenated string.
Conc = vFld
End Function
I have to say that I have used this code in several queries with success.
Here is the problem:
I have 3 queries ,1st query to retrieve several fields about customers ,the 2nd query that uses the 1st query and retrieves some more fields from other tables and the 3rd query in which i use the Conc function.The 3rd query retrieves data from the 2nd query.
Till here everything is ok. The conc function works great.
But if i use the Between 2 dates in the WHERE clause of a date field of the 1st query to filter records i get the following error only when i run the 2nd query.If i run only the 1st query it works,but if i run the 2nd query which uses the 1st it crashes.
RUN TIME ERROR 80040e10 - "No Value Given To One or More Required Parameters" ,when i open the debugger the th following line is highlined :
rs.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly
Here is the sql code of the 2nd and 3rd queries(i have ommitted the 1st query):
2nd query
SELECT 1st_query.customer_id, 1st_query.surname, 1st_query.name, 1st_query.account_number, actions.action_internal_code, activity_codes.activity_name, 1st_query.details,actions.action_date, actions.action_internal_code AS CODE
FROM 1st_query INNER JOIN (activity_codes RIGHT JOIN actions ON activity_codes.activity_code = actions.action_internal_code) ON 1st_query.account_number = actions.ypothesi_id
WHERE (((actions.action_internal_code) Not In ('002','003')) AND ((actions.action_date) Between Format([Forms]![new_actions]![txt_box_start_date],"dd/mm/yyyy") And Format([Forms]![new_actions]![txt_box_end_date],"dd/mm/yyyy")));
3rd query
SELECT 2nd_query.customer_id AS ID, 2nd_query.surname AS SURNAME, 2nd_query.name AS NAME, Conc("2nd_query.account_number","2nd_query.custome r_id",2nd_query.customer_id,"2nd_query") AS ACCOUNT, Conc("2nd_query.activity_name","2nd_query.customer _id",2nd_query.customer_id,"2nd_query") AS ACTION, 2nd_query.details AS DETAILS, 2nd_query.action_date
FROM 2nd_query;
The 2 txt boxes i use have Date/time format and i have also used the 'Between' without the Format Function but the result is the same.
I can't find why this error occurs.Probably because of the 'Between' and the date fields???
It is weird because it occurs only when i use the 'Between' sentence to filter the results of the 2nd query before running the 3rd query.If no 'Between' is used everything is ok and the 3rd query concatenates records fine.
I have to mention that all fields in all tables are text and date,no numeric.
Please Guys ,i am desperate ,i would be grateful if anyone could give me a solution.
Thank you in advance.