Changing Date/Time Property on Linked Table?

  • Thread starter Thread starter Xavier
  • Start date Start date
X

Xavier

I was wondering if it's possible to do the following. I'm working with
a linked table that has both the date and time in the field I'm working
with (let's call it CompDate). In order to run my query properly, I
only need the date when linking to my other table (to field "CalDate").
Is there a way to manipulate the linked table to only pass the date
without actually removing the time (since I can't edit the linked table
to only show date)? I tried DateValue([table1]![CompDate]) but that
returned an empty query. Thanks for the help!

Xavier
 
Time is stored as a decimal in many databases and the date as a whole number
(Integer).

Use this and see if it works on your data --
CVDate(Int([YourDateField]))
 
Hi Karl,

I tried that but it's still not working. I went ahead and generated a
simple query to output my dates using the expression you provided
(entered it in design view) and the field still displays both date and
time. Any other suggestions?

Xavier

KARL said:
Time is stored as a decimal in many databases and the date as a whole number
(Integer).

Use this and see if it works on your data --
CVDate(Int([YourDateField]))

Xavier said:
I was wondering if it's possible to do the following. I'm working with
a linked table that has both the date and time in the field I'm working
with (let's call it CompDate). In order to run my query properly, I
only need the date when linking to my other table (to field "CalDate").
Is there a way to manipulate the linked table to only pass the date
without actually removing the time (since I can't edit the linked table
to only show date)? I tried DateValue([table1]![CompDate]) but that
returned an empty query. Thanks for the help!

Xavier
Click to expand...
Click to expand...
 
Nevermind, got it working. Thanks!
Hi Karl,

I tried that but it's still not working. I went ahead and generated a
simple query to output my dates using the expression you provided
(entered it in design view) and the field still displays both date and
time. Any other suggestions?

Xavier

KARL said:
Time is stored as a decimal in many databases and the date as a whole number
(Integer).

Use this and see if it works on your data --
CVDate(Int([YourDateField]))

Xavier said:
I was wondering if it's possible to do the following. I'm working with
a linked table that has both the date and time in the field I'm working
with (let's call it CompDate). In order to run my query properly, I
only need the date when linking to my other table (to field "CalDate").
Is there a way to manipulate the linked table to only pass the date
without actually removing the time (since I can't edit the linked table
to only show date)? I tried DateValue([table1]![CompDate]) but that
returned an empty query. Thanks for the help!

Xavier
Click to expand...
Click to expand...
Click to expand...
 
I posted that I got it working, but I was only partly right. I managed
to simply display my date/time field in a query only displaying the
date. However, when I try linking it, I go back to my original problem
of getting a blank query. Here is my SQL code so maybe you guys can
see what I'm doing wrong:

SELECT [tbl Weeks].[Last Date]
FROM [tbl Weeks] INNER JOIN [Table1] ON [tbl Weeks].[Cal Date] =
[Table1].wkQue
WHERE ((([Table1].wkQue)=CVDate(Int([wkQue]))))
GROUP BY [tbl Weeks].[Last Date];


[tbl Weeks].[Cal Date] only has a date value
[tbl Weeks].[Last Date] only has a date value. This field will be used
to display a count for my work week (week ending on Thursday).
[Table1].[wkQue] has both date and time

Xavier
 
Try this --
SELECT [tbl Weeks].[Last Date]
FROM [tbl Weeks] INNER JOIN [Table1] ON [tbl Weeks].[Cal Date] =
CVDate(Int([Table1].[wkQue]))
GROUP BY [tbl Weeks].[Last Date];

It will tell you it can not display the join in design view.
----------ALTERNATE-----------
An alternative is to create a select query that outputs a field --
Date_Only: CVDate(Int([Table1].[wkQue]))

Then use that query instead of Table1 directly in your query joining with
[tbl Weeks].

Xavier said:
I posted that I got it working, but I was only partly right. I managed
to simply display my date/time field in a query only displaying the
date. However, when I try linking it, I go back to my original problem
of getting a blank query. Here is my SQL code so maybe you guys can
see what I'm doing wrong:

SELECT [tbl Weeks].[Last Date]
FROM [tbl Weeks] INNER JOIN [Table1] ON [tbl Weeks].[Cal Date] =
[Table1].wkQue
WHERE ((([Table1].wkQue)=CVDate(Int([wkQue]))))
GROUP BY [tbl Weeks].[Last Date];


[tbl Weeks].[Cal Date] only has a date value
[tbl Weeks].[Last Date] only has a date value. This field will be used
to display a count for my work week (week ending on Thursday).
[Table1].[wkQue] has both date and time

Xavier
I was wondering if it's possible to do the following. I'm working with
a linked table that has both the date and time in the field I'm working
with (let's call it CompDate). In order to run my query properly, I
only need the date when linking to my other table (to field "CalDate").
Is there a way to manipulate the linked table to only pass the date
without actually removing the time (since I can't edit the linked table
to only show date)? I tried DateValue([table1]![CompDate]) but that
returned an empty query. Thanks for the help!

Xavier
Click to expand...
Click to expand...
 
Thanks for your help Karl, got it working now (for sure this time)!

KARL said:
Try this --
SELECT [tbl Weeks].[Last Date]
FROM [tbl Weeks] INNER JOIN [Table1] ON [tbl Weeks].[Cal Date] =
CVDate(Int([Table1].[wkQue]))
GROUP BY [tbl Weeks].[Last Date];

It will tell you it can not display the join in design view.
----------ALTERNATE-----------
An alternative is to create a select query that outputs a field --
Date_Only: CVDate(Int([Table1].[wkQue]))

Then use that query instead of Table1 directly in your query joining with
[tbl Weeks].

Xavier said:
I posted that I got it working, but I was only partly right. I managed
to simply display my date/time field in a query only displaying the
date. However, when I try linking it, I go back to my original problem
of getting a blank query. Here is my SQL code so maybe you guys can
see what I'm doing wrong:

SELECT [tbl Weeks].[Last Date]
FROM [tbl Weeks] INNER JOIN [Table1] ON [tbl Weeks].[Cal Date] =
[Table1].wkQue
WHERE ((([Table1].wkQue)=CVDate(Int([wkQue]))))
GROUP BY [tbl Weeks].[Last Date];


[tbl Weeks].[Cal Date] only has a date value
[tbl Weeks].[Last Date] only has a date value. This field will be used
to display a count for my work week (week ending on Thursday).
[Table1].[wkQue] has both date and time

Xavier
I was wondering if it's possible to do the following. I'm working with
a linked table that has both the date and time in the field I'm working
with (let's call it CompDate). In order to run my query properly, I
only need the date when linking to my other table (to field "CalDate").
Is there a way to manipulate the linked table to only pass the date
without actually removing the time (since I can't edit the linked table
to only show date)? I tried DateValue([table1]![CompDate]) but that
returned an empty query. Thanks for the help!

Xavier
Click to expand...
Click to expand...
Click to expand...
 
Back
Top