Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
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
 
Old June 5th, 2006, 07:42 AM
Registered User
 
Join Date: Jun 2006
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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

 
Old June 5th, 2006, 09:32 AM
Authorized User
 
Join Date: May 2006
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

What data type is Scheduled_Appointment.App_Date?

Is it a string or is it date/time?


 
Old June 5th, 2006, 10:13 AM
Registered User
 
Join Date: Jun 2006
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old June 5th, 2006, 10:15 AM
Registered User
 
Join Date: Jun 2006
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

srry a *datetime

 
Old June 5th, 2006, 10:19 AM
Registered User
 
Join Date: Jun 2006
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

:D i must be nervous or somthing lol.

Scheduled_Appointment.App_Date is of type string.

 
Old June 7th, 2006, 09:14 AM
Authorized User
 
Join Date: May 2006
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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 & "'"

 
Old June 7th, 2006, 02:31 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old June 9th, 2006, 07:30 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem converting string to Date. akhilhp ASP.NET 2.0 Basics 2 July 6th, 2008 08:02 AM
Prevent converting empty textbox string to date. fizzerchris ASP.NET 2.0 Basics 2 May 18th, 2007 06:52 PM
Converting an ASCII character to binary? skyraider Visual Basic 2005 Basics 0 May 7th, 2006 08:20 PM
Converting from character to smalldatetime x_ray SQL Server 2000 2 November 26th, 2005 06:01 AM
Converting Unicode to Character RobinR Classic ASP Basics 4 August 6th, 2004 11:40 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.