?
Solved

Pass Through Query Formatting

Posted on 2011-10-11
8
Medium Priority
?
390 Views
Last Modified: 2012-05-12
In a passthrough query I am pulling a text field from SQL Server that  always contains a date value.  Is there a way, in the passthrough syntax, that I can format this as a date so it is treated as a date when it arrives in Access?  Something similar to cDATE, but for SQL?

Thanks...
0
Comment
Question by:snyperj
  • 3
  • 3
  • 2
8 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36952142
You can use CAST or CONVERT.  For example:

CAST(MyDateString AS datetime)
0
 

Author Comment

by:snyperj
ID: 36952206
Yes, that works.  Unfortunately I am finding that sometimes there is no value which then causes all values in the pass through result to change to #Error.  Can that be accomodated somehow?
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36952478
Are these "no value" records null?  A zero length string?  All spaces?  Text not convertible to a date?  Some combination thereof?

For such cases, what would you want the return value to be?
0
Industry Leaders: 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!

 

Author Comment

by:snyperj
ID: 36953034
They are null and they can stay as nulls.... just don't want them to cause everything else to error.

Also not sure if this matters or not, but I see some entries as 3/15/2011 some as 3/15/11

There is no way to control the input on the business system front end to force any kind of uniformity...

Thx
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36953087
The best you can do is something like this:
CASE ISDATE(YourTextColumnGoesHere)
      WHEN 1 THEN CAST(YourTextColumnGoesHere as datetime)
      ELSE NULL
END

But even that is not bullet-proof.
0
 

Author Comment

by:snyperj
ID: 36955563
How would I incorporate that into a pass through.   Below is the pass through in question, 'Customer_Defined_Value' is the field.
SELECT             ORDER_HEADER.Company_Code,ORDER_HEADER.Customer_No,  ORDER_HEADER.Order_No,CUSTOMER_DEFINED.Field_Name,CUSTOMER_DEFINED.Customer_Defined_Value
                         
FROM               ORDER_HEADER  INNER JOIN CUSTOMER_DEFINED_HDR_INFO ON (ORDER_HEADER.Order_No = CUSTOMER_DEFINED.Order_No) 
                         
WHERE           (ORDER_HEADER.Date_Entered BETWEEN DATEADD(year, -1, GETDATE()) AND GETDATE()) AND (ORDER_HEADER.Customer_No = 'UNI052') AND (CUSTOMER_DEFINED_HDR_INFO.Field_Name = 'DELIVERY DATE')

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36959666
Try it this way:
SELECT  o.Company_Code,
        o.Customer_No,
        o.Order_No,
        c.Field_Name,
        -- c.Customer_Defined_Value,
		CASE ISDATE(c.Customer_Defined_Value)
			WHEN 1 THEN CAST(c.Customer_Defined_Value as datetime)
			ELSE NULL
		END Customer_Defined_Value
FROM    ORDER_HEADER o
        INNER JOIN CUSTOMER_DEFINED_HDR_INFO c ON o.Order_No = c.Order_No
WHERE   o.Date_Entered BETWEEN DATEADD(year, -1, GETDATE()) AND GETDATE()
        AND o_HEADER.Customer_No = 'UNI052'
        AND c.Field_Name = 'DELIVERY DATE'

Open in new window

0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 36959669
Let me try that again:
SELECT  o.Company_Code,
        o.Customer_No,
        o.Order_No,
        c.Field_Name,
        -- c.Customer_Defined_Value,
        CASE ISDATE(c.Customer_Defined_Value)
                WHEN 1 THEN CAST(c.Customer_Defined_Value as datetime)
                ELSE NULL
        END Customer_Defined_Value
FROM    ORDER_HEADER o
        INNER JOIN CUSTOMER_DEFINED_HDR_INFO c ON o.Order_No = c.Order_No
WHERE   o.Date_Entered BETWEEN DATEADD(year, -1, GETDATE()) AND GETDATE()
        AND o_HEADER.Customer_No = 'UNI052'
        AND c.Field_Name = 'DELIVERY DATE'

Open in new window

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

578 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