Manipulating data type from linked table to run query

[ges681 via AccessMonster.com]

I am trying to run a query that works off a range in my query. I based it
off this format:

WHERE yourDateField BETWEEN
CDate(FORMS!formNameHere!ControlNameWithStartingDate) AND
CDate(FORMS!formNameHere!ControlNameWithEndingDate)

I was trying to figure out why it would not work, when i look at the design
view of my linked table i saw that the data types for my start date and end
date fields where Text and not Date. This information is being populated
from databases on a server and I dont think I can get these changed. Is
there anyway you can think of, to get my query to still search for records
between the two date fields. Can I manipulate the data in any way during the
query? Any ideas would be a big help, i cant think of what else to do.
Thanks in advance.

-Gabriel

 

[John Spencer]

It kind of depends on how yourDateField is formatted. You might be able to
use the datevalue function or the CDate function. Both of these will error if
they cannot interpret your datefield value as a date.

WHERE DateValue(yourDateField) BETWEEN
CDate(FORMS!formNameHere!ControlNameWithStartingDate) AND
CDate(FORMS!formNameHere!ControlNameWithEndingDate)

If you get errors using DateValue or Cdate you can try using the ISDate
function to see if what is in YourDateField can be interpreted as a date.

WHERE IIF(IsDate(YourDateField),CDate(YourDateField),Null)
BETWEEN CDate(FORMS!formNameHere!ControlNameWithStartingDate) AND
CDate(FORMS!formNameHere!ControlNameWithEndingDate)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[ges681 via AccessMonster.com]

The Date Value in the table is formated as a date/time stamp...
So it looks like 10-22-2008 22:24:39

Here is a brief look at my SQL statement:
Select...other stuff....Format(CDate(SERVICEREQUESTS.[Completed On]),"Short
Date") AS
[Completed On]

Where...other stuff... ((SERVICEREQUESTS.[Completed On]) Between CDate([FORMS]
![SR_FORM]![txtStart]) And CDate([FORMS]![SR_FORM]![txtEnd])))


But becaue the Where clause mentions the table name it does not work. if I
delete the table name from the Where clause it still does not work. I may
try the DateValue and see if that makes a difference.

Thanks for the idea. I will give it a shot and see what happens...
-Gabriel

It kind of depends on how yourDateField is formatted. You might be able to
use the datevalue function or the CDate function. Both of these will error if
they cannot interpret your datefield value as a date.

WHERE DateValue(yourDateField) BETWEEN
CDate(FORMS!formNameHere!ControlNameWithStartingDate) AND
CDate(FORMS!formNameHere!ControlNameWithEndingDate)

If you get errors using DateValue or Cdate you can try using the ISDate
function to see if what is in YourDateField can be interpreted as a date.

WHERE IIF(IsDate(YourDateField),CDate(YourDateField),Null)
BETWEEN CDate(FORMS!formNameHere!ControlNameWithStartingDate) AND
CDate(FORMS!formNameHere!ControlNameWithEndingDate)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I am trying to run a query that works off a range in my query. I based it
off this format:

[quoted text clipped - 13 lines]

-Gabriel

 

[John W. Vinson]

The Date Value in the table is formated as a date/time stamp...
So it looks like 10-22-2008 22:24:39

Here is a brief look at my SQL statement:
Select...other stuff....Format(CDate(SERVICEREQUESTS.[Completed On]),"Short
Date") AS
[Completed On]

Where...other stuff... ((SERVICEREQUESTS.[Completed On]) Between CDate([FORMS]
![SR_FORM]![txtStart]) And CDate([FORMS]![SR_FORM]![txtEnd])))


But becaue the Where clause mentions the table name it does not work. if I
delete the table name from the Where clause it still does not work. I may
try the DateValue and see if that makes a difference.

Two problems here. You're redefining the Completed On field and calling it
Completed On - a circular reference! You can't define a field based on itself.

The other problem is that the Format() function returns a text string, which
will not work correctly. The text string "9/15/2007" is in fact NOT between
"1/1/2007" and "12/31/2007" - because 9 is not between 1 and 1!

I'd leave the Format() function out of the SELECT clause entirely. If
Completed On is a Date/TIme field in the table (not just a text string
interpreted as a date) it should work correctly.

Perhaps you could post the full SQL string if this doesn't help.

 

[ges681 via AccessMonster.com]

This query is run off a simple form that the user checks of certain
checkboxes depending on which requests they want. Here is the code I am
using in my query.

SELECT DISTINCT SERVICEREQUESTS.Team, SERVICEREQUESTS.[Request Number],
SERVICEREQUESTS.[Request Title], SERVICEREQUESTS.Request_Status, Format(CDate
([SERVICEREQUESTS].[Completed On]),"Short Date") AS [Completed On]

FROM SERVICEREQUESTS

WHERE (((Switch([SERVICEREQUESTS].[TEAM]="Team 1",[FORMS]![SR_Parameters]!
[Team1],[SERVICEREQUESTS].[TEAM]="Team 2",[FORMS]![SR_Parameters]![Team2],
[SERVICEREQUESTS].[TEAM]="Team 3",[FORMS]![SR_Parameters]![Team3],
[SERVICEREQUESTS].[TEAM]="Team 4",[FORMS]![SR_Parameters]![Team4],True,False))
<>False) AND ((SERVICEREQUESTS.[Completed On]) Between CDate([FORMS]!
[SR_Parameters]![txtStart]) And CDate([FORMS]![SR_Parameters]![txtEnd])))
ORDER BY SERVICEREQUESTS.Score DESC;


The reason I tried the format(cdate) and remaned to column as "Completed On"
is because if I did not and only used CDate, the column name somehow got
replaced to Expr 1012.

Like I mentioned before, the column "Completed On" in the linked table is
formatted as text. I want to be able to search between a user inputted start
date and end date on a form.

The Date Value in the table is formated as a date/time stamp...
So it looks like 10-22-2008 22:24:39

[quoted text clipped - 10 lines]

delete the table name from the Where clause it still does not work. I may
try the DateValue and see if that makes a difference.

Two problems here. You're redefining the Completed On field and calling it
Completed On - a circular reference! You can't define a field based on itself.

The other problem is that the Format() function returns a text string, which
will not work correctly. The text string "9/15/2007" is in fact NOT between
"1/1/2007" and "12/31/2007" - because 9 is not between 1 and 1!

I'd leave the Format() function out of the SELECT clause entirely. If
Completed On is a Date/TIme field in the table (not just a text string
interpreted as a date) it should work correctly.

Perhaps you could post the full SQL string if this doesn't help.