Make Table Query- A Julian Date Field

Dec 8, 2005

I am extracting information from a datawarehouse with a make table query. I will run this queary one time to create the table. Then I plan on creating an append queary to extract the same fields on a weekly basis. I want to keep track of the datasets that i extract weekly. I thought I could create a field with a julian date or the week of the year. Does anyone know how I could create this field in my append queary? OR any thoughts on how i can keep track of the records appended each week?

View Replies


ADVERTISEMENT

Julian Date Query

Sep 4, 2006

I recently upgraded from MS Access 2000 to 2003 I now appear to have a problem with my julian date conversion formulae. Everything was working fine but since 01Sept none of my date/time specific queries work. Please can someone advise me of a Julian Day to Calendar date formula for MS Acces 2003. In my date field I have the following :

Date: DateSerial(6,1,[CR10X AWS]!Field2)

Note sure about the 'DateSerial(6,1, part ?

Regards

Richard

View 1 Replies View Related

Going From A Julian Date To Mm/dd/yyyy

Dec 22, 2005

This may be simple but I'm having a real issue with it. I've used

Format([Input Table]!Birth_Date,"yyyy") & Format(Format([Input Table]!Birth_Date,"y"),"000")

to go from a date to a julian but now I need to go from a 4 digit julian date to a mm/dd/yyyy.

For example ... Today is julian date 5356 "year 2005 and 356 days into the year" and I need to change this back to 12/22/2005.

How do you do this :confused: I would rather do it within the query than do any code.

View 4 Replies View Related

General :: User Defined Function Exports Julian Date

Jan 10, 2013

I have created user defined function for lastdayofweek, lastdayofmonth, and firstdayofmonth. When I apply this function to an expression in my query, it returns a julian date. How do I apply the format for the calendar date?

Maybe I am not calling my function correctly. Here's my functions below as well as my expression..

Function LastDayThisMonth()
LastDayThisMonth = DateSerial(Year(Date), Month(Date) + 1, 0)
End Function

Function FirstDayThisMonth()
FirstDayThisMonth = DateSerial(Year(Date), Month(Date), 1)
End Function

[Code] ....

View 3 Replies View Related

Make Table Query - With Date In Table Name

Aug 20, 2007

I have used/am using the "INTO tbl_temp_copy" to create a temp table from a query called tbl_temp_comp. Everytime I run my query it deletes and then creates a new table with the same name - and this is what I have been wanting.

However now I would like to create a table with the date in the table name so:- "INTO tbl_temp_copy_Date()" but obviously this does not work and I do not know if it is even possible?

Anyone able to do this or know that it cannot be done?

View 1 Replies View Related

Adding Field To A Make Table Query

Dec 27, 2005

I have a make table query created from a linked to a table. i want to add field to the make table query that are not fields on the table. How do i insert a created field? Hopefully this is a basic question.....

View 1 Replies View Related

Format Field In Make Table Query

Feb 27, 2007

DB Setup:
Table1: I have a table (Vendor) that has 2 fields (# & Name) with # being an AutoNumber. So only Name is being input via a form. I have formatted the autonumber field as 000;(000).
Table2: A table that is populated via form with invoice info etc and vendor number is added through a drop down combo box (which also has the above format on it)
Table3: Similar to table2, with slightly diff info but still vendor #

Query1: Is a make table that consolidates table 1 & 2 via union on like fields (ie vendor #) This make table also has the format from above in its properties field, although when i open the table it makes (Table4) the vendor field is not formatted as i need it. So 3 appears as 3 not 003.

Query2: takes table4 adds some extra info and exports file (as txt or xls)using outputTo & TransferText macro so that it can be loaded into a Hyperion Essbase system

My problem is that although the field value is formatted as 003 in appearance, when i take it to excel it changes back to 3 when i need it to stay as 003. I would like the make table query to also format the tables field as 000. Is the problem with the autonumber in the orig table or is it simply excel being stubborn when i take it there. If i changed the vendor field to text string in the make table would i still be able to link it back to the orig vendor table to get the names etc (ie number field linked to text field??)
Thanks in advance

View 1 Replies View Related

Insert New Field/Value In A Make Table Query

Sep 24, 2007

Hi all,
stumped on an issue I thought would be pretty straight forwards (again) so i'm hoping to find the answer here! I have a make table query which produces the table using fields from three other tables and it runs fine however I now want to insert a new field with the value "Current" in it, and, if possible, an Autonumebr ID field as primary key. I'd like this is all done in one query so not sure if it will fly but if anyone can advise me I know someone from here can help.

Thanks in advance,
Mitch......

View 1 Replies View Related

How To Set Format Of A Field In Make Table Query

Sep 9, 2015

I have numerous databases that I use with make tables in there, these will often contain Currency values that we need to be set as just General Numbers. We can get it to work in the Query but whenever we run the query, the table it makes always shows up as currency.

Is there a way so that the table created will automatically be just General numbers...

View 2 Replies View Related

Creating A Checkbox Field Through A Make-table Query

May 8, 2006

Hello to everyone,

I have a colleague who executes a make-table query that reads a txt file. While doind this, he wants to convert two columns (binary format) to checkboxes (Yes/No format). Is there a way to do that?

Thanx in advance

View 1 Replies View Related

Lookup Table - How To Make More Than 1 Field From Query Appear In The List

Dec 1, 2014

In Access, when using Look Up function, Table/Query, how do I make more than 1 field from the query appear in the drop down list?I will be doing the same thing for students and some other tables. But for this example I am only wanting to show instructors first and last name and their ID number in the drop down.

I am using

Code:
SELECT instructors.first, instructors.last, instructors.ID FROM instructors;
in my Classes table.

What am I doing wrong?

I would like for it to show their instructor first and last name but actually put the ID in the field as I will be using that key for various relationship based functions. Show the first and last name to the person who will be using the database, but actually put the ID in the field so I can use it as a common field across other tables.

View 1 Replies View Related

Adding Blank Field Through Make Table Query?

Aug 28, 2013

I'd like to know how to add a blank filed (memo), called "comments" that is not pulled in from any existing table, in an existing maketable query which is getting it's fields from different linked tables , so that the destination table that comes out of this query, has all those data along with an extra column for me to add comments.

View 5 Replies View Related

Make + And - Adjust Date In Field

Jun 12, 2012

I am trying to make the + - (numpad) adjust the date of the current text field. The problem I am having is I believe the input mask intercepts the keypress and my keypress event is never fired. Here is my code:

Code:
Private Sub LeaseDate_KeyDown(KeyCode As Integer, Shift As Integer)If (KeyAscii = 107) Then
LeaseDate = DateAdd("d", 1, LeaseDate)
End If
If (KeyAscii = 109) Then
LeaseDate = DateAdd("d", -1, LeaseDate)
End If
End Sub

Access makes the ding noise when I try and press + and - ...

View 4 Replies View Related

Make Field Auto Populate Based On Value Of Field In Another Table

Mar 4, 2008

I'm creating a database that keeps a track of questions and scores.

The questions in the database need to be dynamic and are changed frequently.

I have a scorecard table which keeps a record of scores and the applicable question at the time the record was saved. I need to do this because in 6 months time we may want to provide feedback. As the question may have changed we need to be able to refer back to what the question was.

I want the question field in my scorecard table to populate with the value in my question table.

I have tried a number of things including setting the question field in the scorecard table to a lookup based on the following query:

SELECT tblQ1.Q1
FROM tblQ1;

This works however only as a list or combo box. I don't want the user to have to select the question. I want it to auto populate, is this possible?

View 1 Replies View Related

Tables :: How To Make Calculated Field Based On Field In Another Table

Jun 18, 2014

Table1 contain Two fields (3Months) and (6Months)

Table2 contain Two fields (3Months) and (6Months)

the table 2 is the source of a form that will let the user change the numbers. table 1 should change Date1 and Date2 Fields based on the two fields (3months) and (6months) if i want to make a lookup wizard it should be changed manually and if i make a calculated field i can't find other tables in the expression builder

View 1 Replies View Related

Tables :: Make Calculated Field In One Table That Calculates Values From Another Table Linked By Order ID

May 13, 2015

I have what is probably a stupid question but I've been struggling with this one for a while. I have an ordering database which has an Orders table (containing Order ID, Date, Supplier etc) and an Order Line table within which I have a combo box for Product name, supplier, price, VAT rate, Line price etc. At the moment, I have the order line table as a subform within the Order form (run from the Orders table). The problem I'm having is the subtotal and total fields. At the moment these are in the Order Line table as I cannot figure out how to get these in the Orders table. In summary, can I make a calculated field in one table that calculates values from another table (linked by Order ID)?

View 3 Replies View Related

Reports :: Null Date Field - Make Entire Entry Not Visible On Report

Dec 16, 2013

I am building a report that has a header with several details followed by a detail section.

What I need to do is make the entire entry not visible on the report if one of the dates is null.

So it looks like this

last first title id memb # date a date b
1. smith , john fng 8521 2356224
5/12/2012 6/20/2013
10/2/2013 (is null)

2. neckbone, jim dude 2548 85858412
6/20/2013 5/5/2014
8/5/2013 2/31/2013

On the report i want the 1st entry (smith john) not to be visible due to the null (B) Date.... but i want it to be visible once the date is entered...

View 2 Replies View Related

Hard Code TIME To Selected Date On Form (to Make It Date&time) For My Query Criteria

Aug 17, 2006

Hello buddies :D, do you have any idea how to make this work?

To select data that falls within this criteria of date range between cboDate and cboDate2 (fields on my form). The date in [tblJobDetails]![timeIn] come in this format "08/17/06 10:24 AM", but the cboDate/cboDate2 (takes in date only e.g 08/17/06) what i am after is to evaluate specific hard coded time in addition to the date entered, i.e. even tho, i haven't entered time on the cboDate/cboDate2, I want specific time hard coded where e.g If i select a date range of 08/17/06 and 08/18/06 on my cboDate and cboDate2 it should really be evaluating: 08/17/06 8:00 AM to 08/18/06 8:00 AM.

This is the criteria i curentlly have on my query in design view tha works perfect in selecting date only.
([tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Or [tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Is Null) And ([tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Or [tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Is Null)
How can I incorporate 8:00am to 8:00am into my cboDate and cboDate2. What can i do to make this happen? Your kindness will be greatly appreciated http://www.naijaryders.com/forums/images/smilies/thankyou.gif

View 10 Replies View Related

Erron With Linked Table Query Using Date Field Criteria

Apr 29, 2008

Hello all

I am using Access as a front end (GUI) with a SQL 2005 database and have several linked tables. An user encountered an odd error that I cannot figure out.

He created a query that had criteria on a date. When he tries to run it the query will not do anything. If he closes out of the query and does any other operation he gets the "This Action will reset the current code in break mode." error and will have to close out access before he can do anything else.

I'm found some strange work-arounds but want to fix the problem the correct way. Attached are two examples - the first one causes the error the second one is the work-around that returns the desired results.

Any ideas?

View 1 Replies View Related

Queries :: Update Date Field Stored In Table - Query Not Working

Dec 23, 2013

Why is this update query not working, I'm trying to update a date field stored in a table.

The new date is passed from a txtbox on a form to the update query!!!

SQL code

UPDATE TblDietPlantemp SET TblDietPlantemp.MealDate = [Forms]![FrmSwitchBoard]![txtCusDate];

View 3 Replies View Related

Make-table Resetting Table Field Value?

Nov 5, 2007

Hi,

I have a make table query that deletes the old table every time the new one is made. However every time the new table is made it resets the field property for the “Date” field to “Text” rather than “Date/Time”

Does anyone know how I can avoid this problem please?

I’ve tried an update query but Access won’t allow me to perform the sum function that I require the actual query to perform. Any suggestions would be very much appreciated.

Thank you

Dean

View 4 Replies View Related

Queries :: Date Range Query - Return Lines Where Field Is Blank In Table

Aug 15, 2013

I have a single table with customer information, one of the fields is a date field "LastContacted".

I'm creating a search form with 2 date fields (txtDate1 & txtDate2) to search a date range of the LastContacted field, and I need to write this into the query that the search form uses.

I have written this using Nz so that it can still return results if the search boxes are left blank:

Between Nz([Forms]![frm_AdvancedSearch]![txtDate1],#01/01/1989#) And Nz([Forms]![frm_AdvancedSearch]![txtDate2],#01/01/2999#)

This seems to work and it returns lines from the table where there is a date entered. However some of the fields in the table have no entry in the LastContacted field. How to code this query so that it also returns lines where the LastContacted field is blank in the table?

I have tried:

like "*" & (Between Nz([Forms]![frm_AdvancedSearch]![txtDate1],#01/01/1989#) And Nz([Forms]![frm_AdvancedSearch]![txtDate2],#01/01/2999#)) & "*"

but this returns errors when I try to run it.

I'm using Access 2010.

View 14 Replies View Related

Make Table: Create AutoNumber Field

Feb 19, 2007

Hi All,

I am trying to create a make-table query, with a new AutoNumber field.

I know that if you are creating a new Text field you type FieldName: "" in Field and for a Number field you would type FieldName: [], but what do you type for an AutoNumber field?

View 2 Replies View Related

Make Calculated Field In A Table To Not Allow Duplicates?

Aug 15, 2013

Is it possible to make a calculated field in a table not allow duplicates?

There is obviously no option to select No Duplicates in the field settings?

View 2 Replies View Related

Queries :: How To Add Populated Text Field At Make Table Run

Nov 3, 2014

I can add to my make table query UDate: Date() that will add a populated field to the output table that has today's date in it.

I can also add something similar like this for example V_Num: [V_Number] and when ran, will prompt with a dialogue box to add some data, it will then create the table with that new field name and populated with the text from the dialogue box.

But how can I automate this to skip the dialogue box and just add it to the expression?

View 3 Replies View Related

How To Make A Table Lookup Field Depend On Value In Another Column

Sep 20, 2012

I have a table called Locations that lists Countries and Cities:

USA, Detroit
USA, Chicago
USA, New York
UK, London
UK, Liverpool
UK, Birmingham
France, Paris
France, Le Mans

I then have another table for inputing details on people I know. This table would have columns called Country and City. I've figured out how to make the Country column a "lookup column" so that I can only enter USA, UK or France. But how do I make the City column show only the appropriate list of cities relevant to the country that I've selected (e.g. Paris or Le Mans if France is the selected country).

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved