 |
| Access VBA Discuss using VBA for Access programming. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|
|
June 5th, 2006, 07:42 AM
|
|
Registered User
|
|
Join Date: Jun 2006
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Converting to character from date string
Hi guys, i'm trying get my ms access form input value(a date) to be passed to the database as a character.
here is the code i'm trying to use, but it doesn't seem to work.
Dim gobrs As ADODB.Recordset
Dim str As String
Dim strh As ADODB.Recordset
Set gobrs = New ADODB.Recordset
str = "Select Patient_Info.Kpaids_num,Docket_num, Patient_Info.First_Name, Patient_Info.Last_Name,Scheduled_Appointment.App_D ate, Patient_Info.Home_Number " & _
"From Patient_Info INNER JOIN Scheduled_Appointment ON Patient_Info.Kpaids_num = Scheduled_Appointment.Kpaids_num " & _
"Where Scheduled_Appointment.App_Date = '" & CDate(Me.Text1) & "'"
gobrs.Open str, CurrentProject.Connection, adOpenForwardOnly, adLockBatchOptimistic
Set DataGrid4.DataSource = gobrs
Me.Detail.Visible = True
'gobrs.Close
|
|
June 5th, 2006, 09:32 AM
|
|
Authorized User
|
|
Join Date: May 2006
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
What data type is Scheduled_Appointment.App_Date?
Is it a string or is it date/time?
|
|
June 5th, 2006, 10:13 AM
|
|
Registered User
|
|
Join Date: Jun 2006
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Quote:
quote:Originally posted by nokia_helper
What data type is Scheduled_Appointment.App_Date?
Is it a string or is it date/time?
|
It's a datetime string
|
|
June 5th, 2006, 10:15 AM
|
|
Registered User
|
|
Join Date: Jun 2006
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
srry a *datetime
|
|
June 5th, 2006, 10:19 AM
|
|
Registered User
|
|
Join Date: Jun 2006
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
:D i must be nervous or somthing lol.
Scheduled_Appointment.App_Date is of type string.
|
|
June 7th, 2006, 09:14 AM
|
|
Authorized User
|
|
Join Date: May 2006
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Why are you trying to convert the data in the textbox with CDate? Can't just use the data from the textbox?
"Where Scheduled_Appointment.App_Date = '" & Me.Text1.Value & "'"
|
|
June 7th, 2006, 02:31 PM
|
|
Friend of Wrox
|
|
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
|
|
Here is a problem with ADO and VBA. I think you need to do one of the following three or four suggestions in order to get this to work. You shouldn't have to type the variable with CDate() since the Dim statement takes care of that. VBA is typeful. If this were VBScript, which is typeless, then you would need to type the variable when you took the data.
Dim dDate As Date
dDate = Me.Text1
"Where Scheduled_Appointment.App_Date = #" & dDate & "#"
I think you need the date # signs instead of the string ' signs to get the SQL statement past Jet.
HTH
mmcdonal
|
|
June 9th, 2006, 07:30 AM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
|
|
Just as big picture observation...
Why do you want to convert a date to a string to begin with? If you're storing it as a date, that's the best way to go. That way, you always know you delimit by #, and you can use functions like DateAdd, DateDiff, Month, Year, Day, Weekday, etc.
If you just want to DISPLAY it as a string, merely use the date AS A DATE along with a format statement to get it to look like what you want, e.g. Format(Date(), "Long Date") looks like "Friday, June 09, 2006"; Format(Date(), "m/d/yyyy") looks like "6/9/2006".
Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
|
|
|
