Windows XP weird problem with dates in query!


Joined
Jun 3, 2010
Messages
5
Reaction score
0
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.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Windows XP Weird IE6 Problem.... 3
Windows XP Weird Printer Problem Neeed Help 1
Windows XP What's with this weird error in msn? 0
Windows XP IE Icon gone weird 2
Windows XP Oki dot matrix XP weirdness 2
Windows XP CHALLENGE: Weird screen capture issue... 6
Windows XP The weirds thing i ever saw.. 5
Windows XP Help! PC is Acting Really Weird! 15

Top