Info in Database converted to Chart

[wallis_online]

What I need to do is have the data on the bottom line of my chart to show the full date as it is in the database that I take the data from. In the database it is in the form of 29/March/2005 and this is how I want it to display on the chart. my code is as follows:

Dim db As Database
Dim qdef As QueryDef
Dim rs As Recordset
Dim dbname As String
Dim i As Integer

dbname = App.Path
If Right$(dbname, 1) <> "\" Then dbname = dbname & "\"
dbname = dbname & "FishKeeping.mdb"
Set db = OpenDatabase(dbname)


Set qdef = db.CreateQueryDef("", _
"SELECT Date, PH FROM WaterQuality")
Set rs = qdef.OpenRecordset(dbOpenSnapshot)

rs.MoveLast
NumPoints = rs.RecordCount
ReDim Values(1 To NumPoints, 1 To 2)

rs.MoveFirst
For i = 1 To NumPoints
Values(i, 1) = Format$(rs!Date, "dd/MMMM/yyyy")
Values(i, 2) = rs!PH
rs.MoveNext
Next i

rs.Close
db.Close

Chart1.chartType = VtChChartType2dXY
Chart1.RowCount = NumPoints
Chart1.ColumnCount = 2
Chart1.ChartData = Values

All I get is the error: Type Mismatch and it highlights

Values(i, 1) = Format$(rs!Date, "dd/MMMM/yyyy")

But if I change this line to

Values(i, 1) = Format$(rs!Date, "yyyy")

it displays my chart but the bottom line only display by year. Am i missing something!?

It does all this if my database is set up to display the date as text or in date/time format.

Any help greatly appreciated sorry for the long query !

Hope all this makes sense!

[Comatose]

Well, Think about it like this... The Date, in the format of dd/mmmm/yyyy is going to try to format the date as something like this: 01/0004/2005 (most likely). Now, another option is, have you tried to remove the format$? So it would be set to something like: Values(i, 1) = rs!Date and try that as text.... since the date in the database is already set to the format you want, just try to load the date as a string into the cell. Let me know if that works.

[wallis_online]

Yeah I tried that.... And it spreads the information out along the bottom of the chart - but removes the information up the side of the chart and doesnt show any dates - I have attatched 2 pictures of what the graph looks like, image 1 is as you suggested, removing the format$ and the 2nd image is with the format$ in. Ive not posted much on this site so dont know how the images will turn out. Let me know if youwant more info or even if you want me to send you the program in a zip file to go over the project better.

cheers for the advice though! glad for the help you have given.

[Comatose]

Yes, Please attach the zip so that I can fiddle with it. Along with your database (or one similar without any confidential information).

[wallis_online]

Yes, Please attach the zip so that I can fiddle with it. Along with your database (or one similar without any confidential information).

None of the data is confidential, its all just 'test' information, I havnt commented anything. frmPHChart is the main one to look at as this is where the chart info is, feel free to mess with the rest of it though !

Thankyou very much for the help ! this is much appreciated, its nothing too important so feel free to leave it if ya like!

thankyou sooo much!

Cheers

[Comatose]

Ok, The solution to the date format has been resolved. What I had to do was set a temporary variable to equal the date as it is, and then use that to set it to a new format date. The Date setup in your database is m/dd/yyyy, which I converted in code to MMMM/dd/yyyy. Then, I converted That To The New Setup Date dd/MMMM/yyyy. Here is the code how I went about doing this:

tmpdate = Format$(rs!Date, "MMMM/dd/yyyy")
ndate = Format(tmpdate, "dd/MMMM/yyyy")

Now, this is all well and dandy, but we still have a real big problem, (and this is the reason for the type mismatch error), is that your variable, Value's is declared in the code above as a single. When we change the date from all numbers (m/dd/yyyy) to a mixture of letters and numbers (dd/MMMM/yyyy), this new value becomes a string! A String! So, unfortunately, since the variable is defined as a single, it can not hold or contain a string. You also use that 2 dimensional array, as the reference to your chart itself. If you were able to change the Values array, it will impact the chart, because (from what I can tell) the chart is in need of an array of single's. I'm still trying to find a workaround, but have been so far unsuccessful. However, finding the problem is sometimes the hardest part.... and that part is done!