Concatenating text fields with a WHERE condition
in [Help]
|
Prev: Can't open query in SQL View to correct query ("Join expression not supported")
Next: dirDataCopy error message when converting to Access 2000
From: Dixie on 1 Sep 2005 04:00 Pieter, just ran into a surprising problem that took me half an hour to work out. I am in Australia. Our date format is DD/MM/YYYY. When I run this function, it is using American Date format. So it takes the date from my text boxes and reverses the day and the month (where this is possible). Is there any way around this? dixie <pietlinden(a)hotmail.com> wrote in message news:1125550940.423579.141410(a)z14g2000cwz.googlegroups.com... > You have to pass the StartDate and EndDate into the function, so it > should look like this: > > Function fConcatChild(strChildTable As String, _ > strIDName As String, _ > strFldConcat As String, _ > strIDType As String, _ > varIDvalue As Variant _ > dtmStart As Date, _ > dtmEnd As Date, _ > As String > '--Function body (mostly omitted for brevity!) > End Function > > then after this: > > Select Case strIDType > Case "String": > strSQL = strSQL & "[" & strIDName & "] = '" & varIDvalue & > "'" > Case "Long", "Integer", "Double": 'AutoNumber is Type Long > strSQL = strSQL & "[" & strIDName & "] = " & varIDvalue > Case Else > GoTo Err_fConcatChild > End Select > > You need to drop in the date filtering part of the WHERE clause > strSQL = strSQL & " AND [SomeDate] BETWEEN #" & dtmStart & "# AND #" & > dtmEnd &"#" > > see? nothing to it, right? >
From: pietlinden on 1 Sep 2005 09:19 Here... found one from Allen Browne... so it'll work. 4. Allen Browne Oct 12 2000, 11:36 pm show options Newsgroups: comp.databases.ms-access From: Allen Browne <abro...(a)odyssey.apana.org.au> - Find messages by this author Date: Fri, 13 Oct 2000 12:38:17 +0800 Local: Thurs, Oct 12 2000 11:38 pm Subject: Re: Date Format Michael, my experience suggests that Format() with "#" handles the case where Access misunderstands the data type, but CDate() does not: Function TestDate() Dim strWhere As String Dim varResult As Variant strWhere = "DOB = #" & Format("1/2/00", "mm\/dd\/yyyy") & "#" varResult = DLookup("DOB", "tblCustomer", strWhere) Debug.Print varResult, strWhere strWhere = "DOB = " & CDate("1/2/00") varResult = DLookup("DOB", "tblCustomer", strWhere) Debug.Print varResult, strWhere strWhere = "DOB = #" & CDate("1/2/00") & "#" varResult = DLookup("DOB", "tblCustomer", strWhere) Debug.Print varResult, strWhere End Function Output (with Short Date defined as dd/mm/yyyy in Control Panel): 1/02/2000 DOB = #02/01/2000# Null DOB = 1/02/2000 Null DOB = #1/02/2000#
From: Dixie on 1 Sep 2005 17:28 Well Pieter, I tried all 3 of those and still get the same results. On 31/8/2005, I get the expected result. When I roll the date up to 1/9/2005, I get no result. I found that if I modify the Dev Ashish module to use any of these methods, I get no result at all. If I use the original code you gave me, I get a result for days later than the 12th of each month where you cant roll the date around. I also tried to use the CDate and Format in the query as well. I got the same result no matter what I used. In contol panel, my short date is set to dd/mm/yyyy. These are the 3 possibilities I have tried in the module strSQL = strSQL & " AND [Date] BETWEEN #" & dtmStart & "# AND #" & dtmEnd & "#" 'strSQL = strSQL & " AND [Date] BETWEEN #" & Format(dtmStart, "mm/dd/yyyy") & "# AND #" & Format(dtmStart, "mm/dd/yyyy") & "#" 'strSQL = strSQL & " AND [Date] BETWEEN #" & CDate(dtmStart) & "# AND #" & CDate(dtmStart) & "#" The first, which is not commented out here, is the one you gave me initially. The other 2 are my interpretation of Allen Browne's code. dixie <pietlinden(a)hotmail.com> wrote in message news:1125580743.037147.52800(a)g14g2000cwa.googlegroups.com... > Here... found one from Allen Browne... so it'll work. > > 4. Allen Browne Oct 12 2000, 11:36 pm show options > Newsgroups: comp.databases.ms-access > From: Allen Browne <abro...(a)odyssey.apana.org.au> - Find messages by > this author > Date: Fri, 13 Oct 2000 12:38:17 +0800 > Local: Thurs, Oct 12 2000 11:38 pm > Subject: Re: Date Format > > Michael, my experience suggests that Format() with "#" handles > the case where Access misunderstands the data type, but CDate() > does not: > > Function TestDate() > Dim strWhere As String > Dim varResult As Variant > > strWhere = "DOB = #" & Format("1/2/00", "mm\/dd\/yyyy") & "#" > varResult = DLookup("DOB", "tblCustomer", strWhere) > Debug.Print varResult, strWhere > > strWhere = "DOB = " & CDate("1/2/00") > varResult = DLookup("DOB", "tblCustomer", strWhere) > Debug.Print varResult, strWhere > > strWhere = "DOB = #" & CDate("1/2/00") & "#" > varResult = DLookup("DOB", "tblCustomer", strWhere) > Debug.Print varResult, strWhere > End Function > > Output (with Short Date defined as dd/mm/yyyy in Control Panel): > 1/02/2000 DOB = #02/01/2000# > Null DOB = 1/02/2000 > Null DOB = #1/02/2000# >
From: Bob Quintal on 1 Sep 2005 18:42 "Dixie" <dixie(a)dogmail.com> wrote in news:43166039$1(a)duster.adelaide.on.net: > I have a problem using Dev Ashish's excellent module to > concatenate the results of a field from several records into > one record. > > I am using the code to concatenate certain awards onto a > certificate at the end of the year. I have the code working > fine, except for the fact that when I want to restrict the > entries to awards between certain dates, even though I can use > the restriction in the query that shows the actual records, > when the fConcatChild function runs, it picks up all the > entries, regardless of the date restriction. I tried to run > the table part as a qry rather than a tbl, but no joy. I > think the code inside Dev's module will need to get have the > date restriction in it. I need the type of restriction that > is WHERE Date >start date <End date. > > Does anyone know how to do that within the module. > The code in that module is beyond my expertise. > Dev's code works perfectly well against a query name passed as the strChildTable parameter, so your problem is for some reason other than the code, I see from other replies that you encountered other issues with the date format issues. Did you check that the query has all the fields from your main table and just a filter for the date range. Test it to make sure that that's not where the problem lies. example:. SELECT * from tblSomeTable WHERE dMyDate BETWEEN dLoDate AND dHiDate. -- Bob Quintal PA is y I've altered my email address.
From: Dixie on 1 Sep 2005 19:15
My brain must be fading. After staring at this code for ages, I spotted the mistake I had made in it. The final version has: strSQL = strSQL & " AND [Date] BETWEEN #" & Format(dtmStart, "mm\/dd\/yyyy") & "# AND #" & Format(dtmEnd, "mm\/dd\/yyyy") & "#" I also visited Allen Browne's website and read his help article on date formatting. Very informative. It works just as it should have now. Thankyou very much Pieter for taking the time to dig that piece of code out for me. dixie <pietlinden(a)hotmail.com> wrote in message news:1125580743.037147.52800(a)g14g2000cwa.googlegroups.com... > Here... found one from Allen Browne... so it'll work. > > 4. Allen Browne Oct 12 2000, 11:36 pm show options > Newsgroups: comp.databases.ms-access > From: Allen Browne <abro...(a)odyssey.apana.org.au> - Find messages by > this author > Date: Fri, 13 Oct 2000 12:38:17 +0800 > Local: Thurs, Oct 12 2000 11:38 pm > Subject: Re: Date Format > > Michael, my experience suggests that Format() with "#" handles > the case where Access misunderstands the data type, but CDate() > does not: > > Function TestDate() > Dim strWhere As String > Dim varResult As Variant > > strWhere = "DOB = #" & Format("1/2/00", "mm\/dd\/yyyy") & "#" > varResult = DLookup("DOB", "tblCustomer", strWhere) > Debug.Print varResult, strWhere > > strWhere = "DOB = " & CDate("1/2/00") > varResult = DLookup("DOB", "tblCustomer", strWhere) > Debug.Print varResult, strWhere > > strWhere = "DOB = #" & CDate("1/2/00") & "#" > varResult = DLookup("DOB", "tblCustomer", strWhere) > Debug.Print varResult, strWhere > End Function > > Output (with Short Date defined as dd/mm/yyyy in Control Panel): > 1/02/2000 DOB = #02/01/2000# > Null DOB = 1/02/2000 > Null DOB = #1/02/2000# > |