Access reports from MySQL data via ODBC? Help!

richy240 · #1 (**permalink**) 01-06-05, 12:04

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

healdem · #2 (**permalink**) 01-07-05, 04:07

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

richy240 · #3 (**permalink**) 01-07-05, 09:20

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!

Internet Services

Web Development

Digital Marketing

Consumer Tech