Is it possible to copy a record 'n' number of times, incrementing the date by either days, months, weeks etc?
I have a regular payments table that will need to be edited at some point should either dates, or amounts change, but... regardless of the size of the table, it's not a problem, as these will be moved to a different table and deleted once paid.
Given the example, would it be possible to copy this record and additional 5 times, incrementing the date by one week.
I would like to know if there is any procedure to restrict/stop auto number increment for certain number of record count (say 50), then increment by 1 for next 50 records.
What's the formula we can use to calculate days between no of days between date job is open and today's date in a query. I am using formulat below formula but the DATE_OPEN and Today (todays date) pops us as enter parameter value. I dont want those enter parameter windows pops us. The date job is open is in a linked table and linked table is getting the information from BPCS software. The date in Liinked is in format YYYYMMDD.
Total Days: DateDiff("d",[Date_Open],[Today])
The SQL Details are below. AUNPRDF_AAA802 is a linked table and information gets updated on 12 am everyday
SELECT DateDiff("d",[Date_Open],[Today]) AS [Total Days], AUNPRDF_AAA802.AAAGE AS Age, AUNPRDF_AAA802.AAORD AS [Job Number], AUNPRDF_AAA802.AAWKSP AS Workshop, AUNPRDF_AAA802.AASWKT AS WorkType, AUNPRDF_AAA802.AANAME AS [Customer Name], AUNPRDF_AAA802.AANME AS [Service Admin], AUNPRDF_AAA802.AASDE1 AS Complaint,
create a macro that automatically fills in the next invoice number in sequence",,I'm assuming this macro would look at the value of the previous record and add a one to itI don't want to use a AutoNumber field as I might need
- A table with a number of fields about a course - A course commence date - A number of days the course will take field - No course end date
Is there a way to easy add one field to the other to: Give end date i.e. do I need to write the code to validate all dates which bring the date over a month e.g. start date 29/sep/2007 and number of days 5.
I'm reworking a db to make it web compatible. Right now I'm working on my Price and Sales tables.There are about 900 Sales records, 450 Price records (for about 45 Items).
I have re-done my Price table with an Autonumber Key field. (It had a multi-key which I understand web db does not support.) Each autonumber key represents a Date with new Price for a Company/Item. The Price change Dates are random.
I have put a Foreign Field in my Sales table for the Price key field.
My dilemma is matching the Sales with the Prices.
When the Price Date and Sales Date do not match (at least half of them don't match), I need to look back in the Price table to the max Date BEFORE the Sales Date in the Sales table for that Company/Item in order to select the correct Price key.
I've been asked to get some information from my database and I'm a bit stuck.
I have a list of refunds in tbl_main and each one includes a dateReceived. I make a record in either tlk_located, tlk_unableToLocate or tlk_bulk depending on the outcome when we're trying to send the money back to whoever it belongs to. Each table has a time stamp (named locatedTime, unableTime and timestamp respectively) field
My manager wants me to report how many entries were unworked on each day in the year, and what the value of them was. An entry is unworked if there is no entry in either of the 3 tables.
So I need a query that lists a range of dates, and for each date counts the number of entries where tbl_main.dateReceived is <= to that date and either has no record in located,unable or bulk or has a record with a timestamp > than the date. (It has been processed now, but hadn't been on the date we are looking at)
I can manage a query that looks at a certain date that it prompts for on each run:
Code: SELECT Count(tbl_main.trust2PK) AS CountOftrust2PK, Sum(tbl_main.amountRefunded) AS SumOfamountRefunded FROM ((tbl_main LEFT JOIN tlk_located ON tbl_main.trust2PK = tlk_located.trust2FK) LEFT JOIN tlk_unableToLocate ON tbl_main.trust2PK = tlk_unableToLocate.trust2FK) LEFT JOIN tlk_bulk ON tbl_main.trust2PK = tlk_bulk.trust2FK WHERE (((tbl_main.dateReceived)<=[cutoffDate]) AND ((tlk_located.locatedTime) Is Null Or (tlk_located.locatedTime)>[cutOffDate]) AND ((tlk_unableToLocate.unableTime) Is Null Or (tlk_unableToLocate.unableTime)>[cutOffDate]) AND ((tlk_bulk.timeStamp) Is Null Or (tlk_bulk.timeStamp)>[cutOffDate]));
I would like a query that lists all dates in a range, and shows the same information for each day listed.
I need to know how to auto enter the date of the previous record into a new record. I have this db for my vehicle log sheets and I submit monthly a claim for the business km from my company. I usually cant remember the date of the previous claim and have to scroll back until I find the field with a date. It will be useful if the date was copied into every new record until I edit the date when I submit the claim. What I ask is during the new month all the dates will be the same until I submit the claim. I do not know how to do this, and yes I have done a search.
I would like to convert a date field to a number in a query. What I am trying to do is take 2 date fields and see how many days they are apart. For example if one date is 3/1/07 and the other is 3/1/06 I would like 365 days to be returned. Thanks for any help!
My challenge is to fix a broken report that results from a query. The query is suppose to bring up all records within a date range. The problem is that the table was designed with the date field being text. Dates such as 06/12/2006 are entered without the beginning 0, so simple queries do not work, and the data is simply 6122006. I think the data needs converted to date first, possibly by extracting and converting. I do not know how to do this because of the missing digit inconsistency. The table cannot be changed directly to a date field without data loss.
I have a query the result of which provides me a date field in the format 12-Oct-2010.I want that this field data to be converted to corresponding numbers such as 40463.
I have a main form and a sub form. when a record is clicked on the sub form I would like to change a textbox on the main form to say "You are on record number 8 of 10.
Do you have any idea's how I can tell what record number they are on;-
i would like to display the date and week number on one of my forms but have no idea about dates and how to code this. can some one help? id appreciate it it beyond me :confused:
I am trying to make a query that finds all records by the "Month Number" and have problems.
What I did: Month(DateField) I got month numbers like I wanted but when I typed in the criteria below, I got February thru December Records, but when I search on anything "up to" 9 it works fine.
I want: Between{month1] and [month2] Month1= 9 (example) Month2= 12 (example) Need results to be from the requested months but any year
I have to imagine it's how the date is converted to an integer, but not sure.
Hi all, and apologies for starting a fresh thread about this, but I can't find exactly what I'm trying to achieve via the search function, although I'm hoping it's just something simple I'm missing.
I have a small database that allows the user to click on a button to go an Add New Records form, which they then fill out. At the moment, the record number gets autofilled and they start entering details. However, one of the details they must enter is a unique record number, consisting of the month, the year, and a number between 01 and 99 (so the first new record created today, for example, would be record 090601, the second 090602, etc. The first next month would be 100601, and so on). At the moment, I have to rely on people to manually enter this number, which requires them to view the current records first to ascertain whether there have been any previous records created that day (to prevent duplications).
What I'd really like is to have the this records box autofill when entering the form with the current month, current year, and then the number of the record for that day. However, it should automatically look at the last record created, and, if there's a match on the month and year, add one to the last 2 digits to ensure a new, unique record. So, for example, I have three records this month, listed as numbers 090601, 090602, and 090603 respectively. I go to create a new record, the box should autofill with the number 090604 and create a new record accordingly.
I would appreciate if someone could help on this problem. What I want to do is to add a field in a form that would automatically show the date of person's birth after entering the persons ID. For example, I enter 39101012345. The first number (3) shows only the gender so I don't need it. The next 6 numbers show the date of persons birth - it is 910101 and it means that the birthdate is 1991.01.01 I need this field in a date format because later I will have to do some calculations concerned with the birthdate of that person.
I have a table that tracks different events and the actual date they occurred. I am trying to write a query that counts the number of each event that occured by month. One issue I am having is if an event had no occurrence during a month I want to return 0 raher than no data for that event.