Ad:

Visual Basic 4 / 5 / 6 Discussion Thread
Unsolved
Views: 3461

Apr 1st, 2005

Info in Database converted to Chart

Expand Post »

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:

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
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
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!

Last edited by alc6379; Apr 6th, 2005 at 11:00 am.

Similar Threads

Insert Dataset into SQL Database. in VB.NET
submiting info into a database for retrieval! in PHP
Good books for creating a chart/graph in VB in Visual Basic 4 / 5 / 6
Database design in Database Design

wallis_online

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

7 posts
since Apr 2005

Permalink

Apr 1st, 2005

Re: Info in Database converted to Chart

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.

Comatose

Team Colleague

Reputation Points: 361

Solved Threads: 214

Taboo Programmer

Offline

2,413 posts
since Dec 2004

Permalink

Apr 1st, 2005

Re: Info in Database converted to Chart

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.

Attached Thumbnails

Click image for larger version

Name: Image1.jpg
Views: 18
Size: 32.9 KB
ID: 1033

Click image for larger version

Name: Image2.jpg
Views: 16
Size: 30.3 KB
ID: 1034

wallis_online

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

7 posts
since Apr 2005

Permalink

Apr 1st, 2005

Re: Info in Database converted to Chart

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

Comatose

Team Colleague

Reputation Points: 361

Solved Threads: 214

Taboo Programmer

Offline

2,413 posts
since Dec 2004

Permalink

Apr 1st, 2005

Re: Info in Database converted to Chart

Quote originally posted by Comatose ...

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

Attached Files

Water Quality Results.zip (24.1 KB, 37 views)

wallis_online

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

7 posts
since Apr 2005

Permalink

Apr 2nd, 2005

Re: Info in Database converted to Chart

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:

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
tmpdate = Format$(rs!Date, "MMMM/dd/yyyy")
ndate = Format(tmpdate, "dd/MMMM/yyyy")
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!

Comatose

Team Colleague

Reputation Points: 361

Solved Threads: 214

Taboo Programmer

Offline

2,413 posts
since Dec 2004

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in Visual Basic 4 / 5 / 6 Forum Timeline: Can anybody give answers for these 2 queries ??!!??

Next Thread in Visual Basic 4 / 5 / 6 Forum Timeline: How can I refresh DataBase without closing Form Attached with my Coding

DaniWeb Message

Cancel Changes

User Name
Password