how shall i define my new Date or numbers.Now "yyyyXX" this is my date "y" is year 4 digits and "XX" is my problem 2 digits. I ll give an example,
(XX = 01....36) XX can be max 36 and min 01 of course. f. Exp. 201436 and next number shall be 201501.
the biggest problem is different of these number with basic math 201501-201436= 65 but for me it should be 01... The Question is; How can define these ??
I have come across a weird event from the 1st July.
I have a form that inputs dates into the table.......nothing special.......
Form is totally unbound and the insertion to the table is done via DoCmd.RunSQL Insert every thing has been fine until the 1st of July at this point it started saving the date into the table in American format 07/02/2013 instead of UK 02/07/2013 There have been no changes to the database at all to give me a reason why this has happened.
The Table fields are set to Date Fields.
The SQL Insert was set to Date ( #" & DATE & "# )
The date displayed on the form is in the correct format to get around this I have set the insert of the date as a string ( '" & DATE & "' ) AND how can i reformat the dates that are incorrectly inserted to the correct format as they include the date and time 07/02/2012 11:11:47
There is about 1000 lines so manual change will be a nightmare.
I am writing code in excel VbA that will fetch data from Access table.
04/03/2015 means 4th March 2015 so if I write the sql query as below then it doesn't pull anything from access table.
Code:
strsql = "SELECT DISTINCT printpoolno FROM tblmaster where username='" & Application.UserName & "' AND Date1=#" & Format("04/03/2015", "mm/dd/yyyy") & "#"
MsgBox strsql
What shall I do?
I'm using Access 2007. I am needing to duplicate a field (It is necessary)
I use the following code.
Private Sub Confirm_Course_Number_KeyPress(KeyAscii As Integer)
Me.Confirm_Course_Number = YearMonth
End Sub
It works exactly the way I want, however the date is showing long. What code do I put in for YearMonth to put it in the format of "ddmm"...
I'm trying to pass some dates from an excel userform into access.
The date is chosen using the DTPicker tool ( basically a drop down calender). I have set the property of this to custom format dd/MM/yyyy, however dates get passed to the appropriate field in access in the American format.
In access the date fields are set to Short Date and the example shown for this format is in the UK format. I assign the date to a variable before passing that variable to the update SQL string:
Code:
s1 = Nz(DTPicker1.Value, #1/1/2000#)
I have dimmed s1 as date and then added:
Code:
s1 = Format(Date, "dd/MM/yyyy")
My update string is:
Code:
"SET [Stage 1] = " & "#" & s1 & "#" & " "
I suspect that the nozero function may be the issue but am at a bit of loss atm.
I've written a vba macro that takes data from Excel, processes it into an XML file & then imports into Access.All fields were fine apart from the date field, which appeared in the Access table as "00:00:00"...The date in the XML was appearing as 01/01/2014.
Code:
dateTemp = [date here]
xmlFieldElement.Text = Year(dateTemp) & "-" & Format(Month(dateTemp), "00") & "-" & Format(Day(dateTemp), "00") & "T00:00:00"
This produces 2014-01-01T00:00:00...Note, putting the date into reverse order without the formatting also causes the import to result in 00:00:00
I am new to access 2010. I have a table called "Forecasts", and I wish to update the timestamp with todays date, whenever the user makes a change to the qty of goods forecasted.
I have been researching online for the solution to the correct format to datetime, but it doesnt seem to work.
Code:
Dim todayDate As Date
todayDate = Date
Dim sqlString As String
sqlString = "UPDATE [Forecasts] " & _
[Code] .....
The messagebox shows:
UPDATE [Forecast] SET Branch_Plant=123, Item_Number_Short='222', Description='AAA', UOM='EA', Estimated_Cost=123, Requesting_Business_Unit='AAA', End_Customer='CCC', Project ='Secret', Timestamp=#26/12/2013# WHERE ID =24
Then I hit a syntax error. Whats wrong with sqlString?
I wanted to assign the field "Number of magazine" with special format based on date/time format but showing only year and month in the format: "yyyy-mm".
So in property of this field in format I put yyyy-mm and in input mask I type 0000-00;;-
I also created the form based on the table containing above field and I defined format and input mask for corresponding formant in the same way like at the table.
But if I try to type date for example 2014-01 in text box of the form it comes up with the full date 2014-01-01. Why does it do like this? What do I do incorrectly?
I have a list of dates in the mm/dd/yyyy format and I am looking to get it into the fiscal format of yyyyww. I am able to do this with the datepart and format functions, but I need to make it so that the fiscal month begins in January but the first week starts if there are three or more days in the week. For instance if Jan 1st is a Friday then this stands as the first fiscal week, if it is a Saturday then it does not count as the first week.
datepart and format functions have the Use the first week in the year that has at least 4 days for the firstweekofyear option but I need it where it has at least three to make it work.
Using access 2010; i have a form that includes a date field. Is there a way to force the user to only choose a month end date? When the user clicks the date from the popup, they may use 9/1/2013 when the mgr. want them to use only 8/31/2013. I am thinking validation field to put a msg but want to be able to force it not the option.
View 2 Replies View RelatedI have a date and time stamp in a Date/Time field of General Date format (3/1/2006 7:52:25 AM).
I wish to select query on the table's Date/Time field by date portion only (3/1/2006) and not include the time portion (7:52:25 AM) of the field.
Using this expression in the query's criteria - "Between [Enter Start Date: (MM/DD/YY Format)] And [End Date: (MM/DD/YY]" will not return the date ranges as desired without also typing in the full time string.
How can the date integer be parsed out and the query properly expression ed on the criteria field without using VB?
On my report, I want rows with expired dates to stand out by having their borders thicker. Something like:
Code:
If Me.txtDate < Date() Then
Me.txtDate.BorderWidth = 6
End If
When I do that, it ends up formatting every txtDate field in the report if one of them passes the If criteria instead of only ones that are expired.
Hi,
I seem to be stuck on the simplest of tasks with both google and forum searches coming up with information that’s either too simple or too complex for my needs (not time wasted though because I’m learning all kinds of related stuff).
How do I join my tables with one-to-one relationships?
[edit] Doh, got this one now - by indexing without duplicates then saying all in one have to link to one in another:D
How do I define both fields in a linking table as composite or two field primary key?
I have this table:
intSalesID (Number, Indexed: (Yes No Duplicates)
intCarID (Number, Indexed: (Yes No Duplicates)
intQuantitySales (Number, Indexed: (No)
This table is a cross table from the two tables tblCars and tblSales where the intSalesID and intCarID are Primary Keys. In this table I didn't set the P.K. I saw databases that have P.K. in tables like this deined from the two fields (intSalesID and intCarID, would be in my case). I wanted to ask what is the difference if I leave this table like this and if I set this two fields to form a P.K.? What is the difference in these twi cases? What do I get and what I don't, if I do either way? Thnx.
Hi all - I have a suite of reports which provide customer details. They all require the user to input the name of the customer.
The way it works is that I have a query which returns everything for the customer.
Then I have other queries based on the first query which bring back the specific information I want regarding the customer.
This works fine when the customer's name is entered using [Enter Customer Name] in the criteria of the relavant field in the first query.
However, because a user might want to run several different reports on the same customer it becomes a chore for them ot repeatedly have to enter the customer name.
My solution is to have an unbound field in the reports menu for the customer name and to use this as the criteria in the query, so it now reads [Forms]![FrmMenu]![Text42].
The first query runs fine, but the queries based on it are suddenly not returning any records.
There is undoubtedly a simple explanation for this but as I'm quite dim, I can't see it. Could somebody enlighten me and maybe even suggest a solution?
Hello all,
I was wondering what should I define a chechbox as if I want to make it a variable, so that when it is checked (= -1), I can check for the -1 and loop somewhere.
Thanks
Hello all,
I was wondering what should I define a chechbox as if I want to make it a variable, so that when it is checked (= -1), I can check for the -1 and loop somewhere.
Thanks
Hello all,
I was wondering what should I define a chechbox as if I want to make it a variable, so that when it is checked (= -1), I can check for the -1 and loop somewhere.
Thanks
Hi,
I am trying to auto-import data w/ a single-click from several Excel sheets. A sample line:
DoCmd.TransferSpreadsheet acImport, , "tblTS1", filename, True, Product!A1:H100
where
tblTS1 - tbl where I'm putting the data
filename - c: est.xls
Product!A1:H100 - range of data I want in sheet Product
I am having problems w/ the "range" portion of Transferspreadsheet (I know it's this portion, because it works when I pre-name the range in Excel). I have tried all sorts of variations on the syntax:
Product!A1:H100
"Product!A1:H100" or 'Product!A1:H100'
"Product" & ! & "A1" & : "H100"
"'Product'" & ! & "A1" & : "H100"
Product & "!" & A1 & ":" H100
I get the error msg:
The MS Jet database engine could not find the object '$:'. Make sure the object exists and that you spell its name and the path name correctly." or "Syntax error"
I wanted to mistake-proof the data transfer, so I did not want to:
- predefine the range in Excel (under InsertNameDefine)
- have the user enter values in text boxes
Any help w/ the syntax would be greatly appreciated!
Cheers,
dvs :confused:
I have a database about when computers have been installed
I have a form
It has a drop down list with Januray through to December
I want the user to select a month and a year and then click the command button and it will run a query displaying all the computers installed in the month of that year
any ideas people?
When I run this query, the fields in the new table contains 253 characters. How do I specify in the SQL command that the field Carg2 and Carg3 will contain 3 charachters ?
SELECT dbo_allesc.AccReport, Left([AccReport],2) AS Carg2, Left([AccReport],3) AS Carg3,.........................
INTO regTabell
Thank you, Tor
We have a db of salmon survey data where catch data is recorded by date, site, etc.
Historically, we have used ‘sampling periods’ to group our results into blocks of time that can be compared from year to year, and within year.
Now, having frequented this forum I’ve read that we shouldn’t store information in tables that can be calculated from fields and I want to try to follow that advice in this instance but am having trouble working this out.
Here’s how we define a sampling period.
1.The start date of the first sampling period in a sampling year is the last Monday in the most recent November.
2.Each sampling period is 14 days long.
What I want to do is have a query use the date of a set to calculate what sampling period the set belongs to. I have an idea that I could use a datediff function to calculate this if I can figure out the ‘last Monday in the previous November’ part. I’ve searched this forum and googled but am having trouble finding anything that I can adapt to this as a query expression.
Anyone care to help a fish bio out? Much obliged :)
Hello Everyone,
I am in need of your vast array of knowledge and experience. I have been fighting with this Access report for a few days now, and I've finally decided to ask for your help.
I am running a report which provides a look at a set of 12 survey results. The 12 items are numerical in nature and are decimals with 2 decimal places. What I am trying to do is have Access automatically highlight the top 3 scores in one color, and the bottom 3 scores in another color.
So what I need is an expression that allows Access to discern the top 3 and bottom 3 from the series of 12 scores. in Excel you can identify the top score with this with the formula "Max(A1:A13)", but seeing as in Access we don't have the cell identifiers, this approach will not work.
I know that I will need to use conditional formatting in order to get the cells to highlight. I also see that there is "Max" and "Min" functions in Access as well, and I attempted to create and expression like this "=Max([field 1] AND [field 2] AND [field 3] AND, etc...)" with no luck. I also tried changing the "AND" to "OR", again without luck.
One thing that I should mention is that the report does contain a lot of other data, so I need to be able to apply whatever the best fix is, to only those 12 fields.
Unfortunately I am not accustomed to using code, and as a result I am unsure of how to input it properly. I am certainly open to code solutions, but I would also need a explanation of how to put it in properly.
The easiest solution for me, if possible, would be an expression that I can enter into the conditional formatting wizard to tell it to highlight the values.
If anyone has any ideas, I would appreciate it. I'll buy you a virtual beer :-)
I have to create a control on a form that will be able to change to one of ten colors, some of them are subtle shades. I will be doing this from vba on the control. I know how to do all of this except defining the colors.
I keep seeing the 3 part RGB(xx, xx, xx) etc. but I can't find the values for it that will give me the exact colors that I need!
I also saw a possibility of using a the hex equivalent for the color but I couldn't make that work?
The date format from one of my access tables is as follows:
11051977
9101978
and I need to amend to following formats within my select query:
11/05/1977
09/10/1978
I have tried using formula:
Date of Birth: DateValue(Mid([DOB],7,2) & "/" & Mid([DOB],5,2) & "/" & Mid([DOB],1,4))
that results in an #Error messages
Can anyone help?
Thanks
Hello
Can someone help format the date correctly? I'm currently linking a flat file in access and then creating a table afterwards. The date comes in the following format below:
i.e. 30102006 - txt type
I wanted the date to look like - mm/dd/yy. Thanks for the help.