Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Need assistance passing a date between two fields

Posted on 2011-09-28
18
Medium Priority
?
275 Views
Last Modified: 2012-05-12
I have an ADP with a 2008 SQL database and experiencing problems with the following code trying to pass the Admission Date from one table to populate another.  It is correct in mm/dd/yyyy format in my source table and when it is passed it shows at 12:00:00 AM.  The source field is formatted as DateTime in SQL and the form that I am pulling it from shows it correctly.  I have even gone as far as formatting the text box as shortdate to see if that would remedy the situation.  I have attempted a Convert Function with the same results.  Suggestions?
strSQL = " INSERT INTO MedHistoryPhysicalAdmission ( FAMILY, AdmissionDate )"
    strSQL = strSQL & " SELECT Convert(int," & [Forms]![MedicalHistoryPhysicalAdmissionLookupForm]![Family] & "), "
    strSQL = strSQL & "" & [Forms]![MedicalHistoryPhysicalAdmissionLookupForm]![AdmissionDate] & ";"

    DoCmd.RunSQL (strSQL)

Open in new window

0
Comment
Question by:JasBrad
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 4
  • +3
18 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 36720004
The datetime data type will have a time of 0 (12:00am) if one is not provided.  If you want to truly track only the date, consider using a DATE data type.
0
 

Author Comment

by:JasBrad
ID: 36720035
I originally had the destination field as datatype DATE and ran into difficulties with the statement failing with a data type mismatch.  It would only accept data with a datatype of DATETIME.
0
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 36720041
the canonical format for strings containing datetimes, which are autoconverted to  a datetime field in MS SQL Server is not mm/dd/yyyy but 'yyyymmdd hh:mm:ss'.

I know too little Access VB(A) to tell you what to use to convert your form control value, but now you know the right format it should be easy to accomplish via Year(), Month()  etc. or similar VB functions giving you the date parts.

Bye, Olaf.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 19

Expert Comment

by:Eric Sherman
ID: 36720116
Try this ...

Format([Forms]![MedicalHistoryPhysicalAdmissionLookupForm]![AdmissionDate] , "yyyy-mm-dd hh:nn:ss")


ET
0
 

Author Comment

by:JasBrad
ID: 36720181
Nope, it will not accept Format as a valid function.  Sounded good though.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 36720228
Try this ...

DatePart("yyyy", [Forms]![MedicalHistoryPhysicalAdmissionLookupForm]![AdmissionDate]) & "-" & DatePart("m", [Forms]![MedicalHistoryPhysicalAdmissionLookupForm]![AdmissionDate]) & "-" & DatePart("d", [Forms]![MedicalHistoryPhysicalAdmissionLookupForm]![AdmissionDate])

ET
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 36720274
I've seen this before between Access & SQL Server ...

If the previous post does not work ... try this ...


CONVERT(VARCHAR(8), [Forms]![MedicalHistoryPhysicalAdmissionLookupForm]![AdmissionDate], 112)


ET
0
 
LVL 9

Expert Comment

by:DrewKjell
ID: 36720319
Is there a reason you are converting the datetime value to an int?

IE - " SELECT Convert(int," & [Forms]![MedicalHistoryPhysicalAdmissionLookupForm]![Family] & "), "

Try ET's suggestion here instead of the current line, so replace the above with

" SELECT Convert(varchar(8)," & [Forms]![MedicalHistoryPhysicalAdmissionLookupForm]![Family] & ", 112), "
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 36720330
You must use the date delimiters of SQL Server:

strSQL = strSQL & "'" & [Forms]![MedicalHistoryPhysicalAdmissionLookupForm]![AdmissionDate] & "';"

/gustav
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 36720353
Also .... for Date & Time, try this ...

CONVERT(VARCHAR(19), [Forms]![MedicalHistoryPhysicalAdmissionLookupForm]![AdmissionDate], 120)

Plus ... as mentioned by cactus data ... you will need to enclose the date in single quotes ... "'"


ET
0
 
LVL 30

Accepted Solution

by:
Olaf Doschke earned 1000 total points
ID: 36813611
If we're talking Access here, there is no Convert() function, but you can use:

Format([Forms]![MedicalHistoryPhysicalAdmissionLookupForm]![AdmissionDate], " 'yyyymmdd hh:mm:ss' ")

or for just the date:

Format([Forms]![MedicalHistoryPhysicalAdmissionLookupForm]![AdmissionDate], " 'yyyymmdd' ")

The single quotes are intended, they are essential for SQL Serer to understand this as string literal representing a date/time vs a number, which just conincidentally has the year,month and day in it, especially but not only in the second example just containing yyymmdd.

Overall this would be:

strSQL = " INSERT INTO MedHistoryPhysicalAdmission ( FAMILY, AdmissionDate )"
    strSQL = strSQL & " SELECT Convert(int," & [Forms]![MedicalHistoryPhysicalAdmissionLookupForm]![Family] & "), "
    strSQL = strSQL & Format([Forms]![MedicalHistoryPhysicalAdmissionLookupForm]![AdmissionDate], " 'yyyymmdd' ") & ";"

    DoCmd.RunSQL (strSQL) 

Open in new window


Bye, Olaf.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 36813628
> If we're talking Access ..

We are, Olaf, but it is an ADP => syntax of SQL Server

/gustav
0
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 36813866
Yes, but the code creating the sql query is Access, isn't it? SQL Server does not use & fro string concatenation, but VB does. Note that I put the Format() function outside of the string, it generates the correct data literal.

Bye, Olaf.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 36814034
You are right. It would work.

/gustav
0
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 36815175
@JasBrad

As you already tried the Format() function, the question is how. Of course SQL Server does not know it, but Access (VB) does. And it depends on the  [Forms]![MedicalHistoryPhysicalAdmissionLookupForm]![AdmissionDate], being a Date/Time value and not a string, so it depends how you have created your Access Form and it's AdmissionDate field.

Independant of your form and SQL Server locale settings this will work:

strSQL = " INSERT INTO MedHistoryPhysicalAdmission ( FAMILY, AdmissionDate )"
strSQL = strSQL & " SELECT 1, '20110929' "

DoCmd.RunSQL (strSQL) 

Open in new window


SQL Server does some automatic type conversions, mostly starting from a string literal, eg you can specify a uniquidentifier as a string and you can use the 'yyyymmdd' format for dates, but not without the single quotes. 20110929 would be interpreted as a number of course. '20110929' can also be the string, but if you select this into a datetime or daet field, SQL Server does convert it.

Open SQL Server Management Studio and click the "New Query" Toolbar button.
Then write: Select 29/09/2011
execute that.
It results in 0, because SQL Server doesn't interpret this as a date, but as a mathematical expression.

Select 1.0 * 09/29/2011
yields the more precise result 0.00015432322

See? 9 divided by 29 divided by 2011.

What you can do in general is test what SQL Server Management Studio says about your queries, simply copy the content of strSQL into the windows cliptext and paste it into a new query window to execute it there.

Execute this in SSMS:

select '20110929'

declare @date as DateTime
set @date = '20110929'
select @date

The first Select will result in the string, SQL Server does not convert, if there is no reason to do so.
The second Select results in the datetime value, as the variable set is of that type.

Bye, Olaf.
0
 

Author Comment

by:JasBrad
ID: 36817149

Olaf,
The problem I am running up against is that the function Format is not recognized or at least that is the error message I am getting when I attempt to put your code in.  This is what I have to use with the quotes to make it work and then get the error message.  Normally I would pass a parameter to a Stored Procedure to make it work but Access it blocking the value from being passed for some reason, so I am doing everything in VBA.
strSQL = " INSERT INTO MedHistoryPhysicalAdmission ( FAMILY, AdmissionDate )"
    strSQL = strSQL & " SELECT Convert(int," & [Forms]![MedicalHistoryPhysicalAdmissionLookupForm]![Family] & "), "
    strSQL = strSQL & "format(" & [Forms]![MedicalHistoryPhysicalAdmissionLookupForm]![AdmissionDate] & ", 'yyyymmdd');"

    DoCmd.RunSQL (strSQL)

Open in new window

0
 

Author Comment

by:JasBrad
ID: 36817264
Sorry Olaf, I wasnt reading your code properly and putting in too many quotes.  I now realize what I was doing wrong.  Thank you so very much.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 36817447
JasBrad ... I answered your question yesterday as shown below ....

>>>>>Try this ...

Format([Forms]![MedicalHistoryPhysicalAdmissionLookupForm]![AdmissionDate] , "yyyy-mm-dd hh:nn:ss")


ET>>>>>


You said ....

Nope, it will not accept Format as a valid function.  Sounded good though.


0

Featured Post

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question