Results 1 to 11 of 11
  1. #1
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Answer To Leesha (Locked post 418879) (2002)

    The thread with <post#=418879>post 418879</post#> has been locked by <!profile=HansV>HansV<!/profile>, so here is the answer.
    To add the start and ending time to the memo field modify the two following lines :
    <pre>strSQL = "Select cl_last, cl_first, Skilltxt,paynm, StartTime,dayofwk " & _
    " From tblVisitReport " & _
    "WHERE(((tblVisitReport.visit_stat) = 'c') And ((tblVisitReport.em_last) = 'turner'))"
    .....
    rstWVR(rstVP!dayofwk) = rstWVR(rstVP!dayofwk) & rstVP!cl_last & ", " & rstVP!cl_first & vbCrLf & _
    rstVP!skilltxt & cbcrlf & rstVP!paynm & vbCrLf & vbCrLf</pre>

    To <pre>strSQL = "Select cl_last, cl_first, Skilltxt,paynm, StartTime,EndTime,dayofwk " & _
    " From tblVisitReport " & _
    "WHERE(((tblVisitReport.visit_stat) = 'c') And ((tblVisitReport.em_last) = 'turner'))"
    ....
    rstWVR(rstVP!dayofwk) = rstWVR(rstVP!dayofwk) & rstVP!cl_last & ", " & rstVP!cl_first & vbCrLf & _
    rstVP!skilltxt & cbcrlf & rstVP!paynm & vbCrLf & rstVP! StartTime & " To " & _
    rstVP! EndTime &
    vbCrLf & vbCrLf</pre>

    For the second question, in the code I post, I use starttime to define in witch record the data has to be added. So you can't leave it out. This being said, you are not obliged to put it on the report.
    Francois

  2. #2
    Star Lounger
    Join Date
    May 2002
    Location
    Patagonia Region Chile
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    data instead of aggregate calculations in Crosstab

    What is it that allows you to show "data" as opposed to numerica values resulting from aggregate funtions in "interior grid"
    your crosstab query result.

    I get this error
    " You tried to execute a query that does not include the specified expresion 'client' as part of an aggregate function."

    This occurs when I change the crosstab row to EXPRESSION in the GUI query designer. If I set it to first. It gives me "-1" in all the "cells" where I wish to see the client's name. It seems that Leeshas crosstab query (in file posted by Francois) does what I want (and a whole lot more) Maybe its simpler than I think.

    I have products that I sell exclusively by city. In the USA a city has multiple zip codes but the left 2 digits roughly define a city. So I want my cross tab to have Left([zipcode],2) accross as column headings... ProductID as row headings and the cell values of Client name (or empty if nobody in that city carries that product)

    Thanks for the help... Jason

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Answer To Leesha (Locked post 418879) (2002)

    Hi Francois,

    I tried the code but got a compile syntax error with this part:

    rstWVR(rstVP!dayofwk) = rstWVR(rstVP!dayofwk) & rstVP!cl_last & ", " & rstVP!cl_first & vbCrLf & _
    rstVP!skilltxt & cbcrlf & rstVP!paynm & vbCrLf & rstVP! StartTime & " To " & _
    rstVP! EndTime & vbCrLf & vbCrLf

    Needless to say I haven't got a clue as to what is missing.

    Thanks for everything,
    Leesah

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Answer To Leesha (Locked post 418879) (2002)

    First of all, put an Option Explicit after the Option Compare database command. This will highlight the cbcrlf as the problem when you compile.

    You will have to change cbcrlf to vbCrLf.

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Answer To Leesha (Locked post 418879) (2002)

    Besides the typo Patt have mention, there is another one.
    Between rstVP! and EndTime there is a space and that should not.
    <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    Francois

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: data instead of aggregate calculations in Crosstab

    Can you post a stripped version of the db so we can see what you have and what we can do with it ?
    Francois

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Answer To Leesha (Locked post 418879) (2002)

    Hi!

    I'm reporting back to say thanks as well to let you know I really don't just copy the code but try to learn from it. After making the changes you and Pat suggested (thanks Pat!) I still got and error message. I studied the code and found there was a space with starttime similar to the one you pointed out with endtime. That fixed the problem. Feeling brave I then went in and took out the reference to payment as I don't need that part in this particular calendar, as I did in the one you first did for me. Of course there remains one more thing totally beyond my scope. The times are formated with seconds included and I only need hours and minutes. The time for the start time field on the as a row setting is formated OK. I'm not sure where to change the format in your code, or why it is coming out that way vs. the way the starttime is formatted in the table.

    Thanks!

    Leesah

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Answer To Leesha (Locked post 418879) (2002)

    In the line writing the data to the memo field, you can use the format function :
    rstWVR(rstVP!dayofwk) = rstWVR(rstVP!dayofwk) & rstVP!cl_last & ", " & rstVP!cl_first & vbCrLf & _
    rstVP!skilltxt & cbcrlf & rstVP!paynm & vbCrLf & Format(rstVP!StartTime, "hh:nn AM/PM") & " To " & _
    Format(rstVP!EndTime, "hh:nn AM/PM") & vbCrLf & vbCrLf
    This time I test the line in the copy of the application I have, so there shouldn't be to much typo's <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    Francois

  9. #9
    Star Lounger
    Join Date
    May 2002
    Location
    Patagonia Region Chile
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: data instead of aggregate calculations in Crosstab

    Thanks
    Here is a zip of my mdb.
    jason

  10. #10
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Answer To Leesha (Locked post 418879) (2002)

    Thanks so much!! It's wonderful!

    Leesha

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: data instead of aggregate calculations in Crosstab

    Very difficult to test something without data. If in the future you send another db, please leave some significant data so we can test real situations.

    Seems to me that if you use a crosstab query, this will become soon complex due to the numbers of columns. I modify your crosstab to show how to do it. Keep in mind that even if you don't sell a product in a city, the column will be created if this article has been sold in another city.

    I create a quick and dirty report based on "Basequery" to show you how you could do this in a report.

    Feel free to post if you have additional questions.
    Francois

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •