Related Topics

Changing Dates In A Table

Using date variables from table in query
I have a table that contains dates (Saturday through Friday). When I set up my query, I would like data returned where the date in a field is between the Saturday date and Friday date. Is there anyway to do this n the query without hard coding the dates in and manually changing the dates every week?


Date day and month being swapped
I am having trouble with entering dates into a database. When I enter a date some of the day and months are swapped. What happens is this:
if I enter 03/05/2009 (3rd of May) it is desplayed as 05/03/2009 (the 5th of March), However if I enter a date where the day is after the 12th of the month this does not happen for example 13/05/2009 stays 13/05/2009.

I have tried changing the input masks and the format of the column and I have even removed these settings, but the computer still does this. I am trying to copy many records from a different database into this one, so manually changing or fixing these dates is not an option.

Can somebody please help me so that I can stop bashing my head against the table


MS Access A Different Between Dates Problem
I have a table (A) with close to 1000 distinct dates. I have another table (B) with hundreds of thousands of records, each of which contains a "From Date" and "To Date".

I need to find all of the records (Dates) in table A that are not encapsulated between any of the From-To dates in table B. In layman's terms, which dates in table A were missed in table B.

Table B may have From-To overlaps, or gaps. There is no relation between tables A & B other than checking if date in table A is between From-To in any record intable B.


How Do I Convert a Date in Access to a Serial Number
I'm using a text data type (for numerous reasons, using date data-type is not an option) for my Dates columns and they were all running fine but after changing data types and changing them back, they all became serial numbers.

Can't figure out how to convert them back to dates whilst still in the text data-type.

Tried using "dd/mm/y/" (works in Excel but not Access?) in the format field for my dates however they stay put as Serial numbers.


Expression do not keep date format
I have a HUGE query (about 5 A4 pages). This query is the source for a Excel Pivot table.

The Pivot tables ONLY contain row labels --> no values.

Most of the fields in the query are expressions like: iif(isnull([FIELD];"";[FIELD]) otherwise I'll receive cells containing "(Blank)" in my Pivot table which confuses the users.

This works fine except for the dates. The dates are formatted to text! In the Pivot table the users can't filter properly on the dates (e.g. sort by oldest to newest etc.). ATMthe dates are sorted by the day no matter month and year.

I have tried setting the fields Format (Property Sheet) to Short Date and Long Date. And I tried changing the expressions to iif(isnull([FIELD];"";Format([FIELD];"Long Date")). I have also tried with "dd-mm-y".


Parameter query as a controlsource
I have a parameter query with 'From' and 'To' dates that I create a report with. Is there anyway to use the From and To dates as a control source for the report ? I'd like the report page to include the dates as a header, since they will be changing


Setting how your computer handles two-digit years in Access
When interpreting two-digit dates, Access makes certain assumptions based on the regional settings in Control Panel. You can alter the way Access interprets two-digit dates by changing these settings.


Working with Complex Queries that Calculate
Whenever you develop a commercial application, there is absolutely no way that you can get by without using date fields. The Video application has Movie Rental date, Return date, Date-of-birth, Report dates. The Dating Service has dates for dates. The Credit card has an Expiry date. There are Birth dates, Hire dates, Delivery dates, Order dates, and so on, and so on.


brute force change dates because nothing else works
I have a huge Microsoft access database front-end tied to a massive sql server back end. Tons of fields, forms, relationships, queries, reports, macros, modules . it is loaded with everything.

The problem is that for a certain set of dates on this form, for whatever reason, the format that is being displayed is Y-MM-DD. Changing the formatting and other options in the field properties does not help. In an attempt to figure out what is causing this, this is what I found:

If I link the SQL tables manually the aforementioned data fields are properly displayed as MM/DD/Y. However, if I use some tried and true code that has proven to work flawlessly over the years to link the SQL tables using the exact same account as the manual link, the fields are displayed as Y-MM-DD.

Apparently, this is some deep and hidden issue here that I am not going to be able to fix because I tried everything and this is what is causing it.

this seems to be a glitch with something that I am not going to be able to fix in my lifetime deep inside Microsoft software

I am wondering if I can try to BRUTE FORCE these dates to MM/DD/Y via some code that can be ran in a module on the form open or on current. changing anything in an attempt to get these dates to be in the format I need them to be will not work whatsoever for these particular dates .

How would I attempt to override this in vba using brute force?

I tried the following to no avail:

Me.[DateField].Format, "MM/DD/Y" along with a few others and they don't render error or change the format.


Comparing 2 columns against 2 other columns
This may be a noob question, but how can you run a query comparing an ID number and a date against another table of ID numbers and dates?

I have everything loaded and ready to go, can easily compare ID vs ID, but one of the tables has a couple dates only per ID (that we need) and the other table has a large amount of dates in there that we do not need.

Basically, trying to match up two columns on the smaller table to two columns on a much large table.


Working with departure dates
I already have a table called TourName and those names are already in the table. One tour may have several date departures during the year and some only one departure. I need to show departure dates (TourDates) for those respective tours and then have the form display those departure dates.

Should I create another date table or just add more columns to the existing TourName table . but I have doubts that would work well.


Working with departure dates
I already have a table called TourName and those names are already in the table. One tour may have several date departures during the year and some only one departure.
I need to show departure dates (TourDates) for those respective tours and then have the form display those departure dates.
Should I create another date table or just add more columns to the existing TourName table . but I have doubts that would work well.


Display actual working days between 2 dates
I'm currently working on a project whereby I need to create a list of all working days between 2 variable dates.

I've tried the usual google searches but that just seems to indicate ways of counting the days rather than displaying them as a list.

I have my start and end dates on one table and just want to use these to generate a table with the working day dates (if that makes sense


changing date automatically in a table field
I have a table of customers and dates when their orders were placed. Is their a way of having a field within said table that shows the order date plus 3 weeks (ie when the order is due for delivery). It would be good if this can be done automatically as soon as the first date is entered in the first field without the operator having to do anything


Fortnightly Dates
I am a little new to VBA. I have a table of start dates and values which are weekly, fortnightly and monthly payments. I want to expand this table into a new table showing all the dates and values for a 12 month period.

eg Start Date 1-4-09 $100 Fortnightly


Graph date range from query
I have a graph which queries data from a table, I need the graph to show the dates in the table with the correct results specific to that date.

However on the graph its displaying dates not in the table so its plotting the results out wrong, I need it to only show the dates in the the table on the bottom axis.


Moving Data In one Row over an X amount of Columns
I'm running a clinical trial with > 600 patients and I have 7 dates listed for visit dates, however going through patient files I have discovered that the database is missing dates and I must add the date in between dates 2 and dates 3, Therefore I'd need to move all dates 3 - 7 all over one column without it affecting the other 599 patients.

I was wondering if there was a way to do so similar to Excel where you could insert a cell within a row and just shifting everything in the columns over once.


Filter data between two dates within a form
What I would like to do is have a command button that allows perhaps a dialog box to pop up where I would enter two dates. After the dates have been entered I would like my form to display only those records within those two dates.


Creating a query result table displayed in the main form
as I am not good at access and desperately seeking for solution.

I am developing a training database which has training courses, participant names, training dates and cost.

I had created a query to display training courses from a range of dates. The user need to select 2 dates to display the course between those two dates.

I have no problem getting the result I want but I want the result to be displayed in the same form where the user keyed in the dates.

Currently when I run my query, a new table will be opened up to display the result.

I had tried to create a form and pulled in the query table in the same form but the result always displayed in a new window. The query table that is in my form is not updated at all


Count Distinct Formula for Access Report
I have a table with various dates across all records (1/1/2012, 2/1/2012, etc) with some dates repeating multiple times. I have a report where I want to enter a formula in an unbound txtbox to count distinct how many different dates there are.

I've looked on net and this forum but usually find answers that involve writing queries or some custom code. I really just want/need a formula (to put in recordsource of txtbox).

So this sample data set would give me a result of 4. There are 6 dates but only 4 unique or distinct dates.