Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    24

    Unhappy Unanswered: Access reports from MySQL data via ODBC? Help!

    I am having some problems with Access reporting using linked tables from MySQL. I am hoping someone can answer a couple questions regarding data types and the like.

    First, how can I display a character on the Access report using a condition? In this case, the Jet (Access) Boolean data type is equivalent to the MySQL TinyInt data type. How can I display a dot or a "Y" in my report if the value of the field is "1" (True) in the DB? I am somewhat new to Access reports, and my past experience in Access is mostly VBA.

    Second, why are date fields that are listed as [NULL] in MySQL CC being displayed as "1" in my access reports? This makes no sense, but I am sure there is some obscure reason for it.

    By the way, I am using MySQL 4.0.18 and Access 2002. My MySQL ODBC version is 3.51

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Sorry can't help you with the Null problem coming out as 1.

    However for the "Y" in reports I would suggest...

    First off are you using pass through queries or JET queries?
    if the latter try the conditional IF statement in the query definition
    Code:
    iif([myControl]=1,"Y","N") as boolMyControl
    in SQL window
    OR
    Code:
    boolMyCOntrol:iif([myControl]=1,"Y","N")
    in the query designer

    if the former then you will probably need to insert soem vba in the reports "detail format"ting event.

    1 leave the control on the report, but make the control invisible (Properties | format | Visible = FALSE)

    Add a text box control for each boolean field you want to set to to Y or N
    name that control something meaningful to you eg tbMyControl

    You then need to add some VBA
    view the code behind the report

    I would reccomend that you always add as a first line to any code module
    Code:
    Option Explicit
    But then you are doing that already aren't you?

    In the left hand combobox look for the detail element, click it
    eg
    Code:
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    if [MyControl]=1 then
      tbMyControl="Y"
    else
      tbMyControl="N"
    endif
    End Sub
    There are probably other ways of doing it (you may be able to move the IIF into the unbound control tbMyControl. Eg set the data source for tbMyControl to
    Code:
    iif([myControl]=1,"Y","N")
    HTH

  3. #3
    Join Date
    Sep 2003
    Posts
    24

    Talking

    Thank you for the reply!

    Actually, I am putting 'Option Explicit' at the top of my modules, but thank you for the recommendation!

    I was actually able to get this one figured out yesterday, using the same thing you recommended here. It works wonderfully except at first I was putting the IIF function directly into the report, which was rendering some unexpected results. Once I realized the mistake and placed IIF in the query itself, things started moving much more smoothly.

    Thanks again for the reply. I appriciate your help!

Posting Permissions

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