i use the format function to display month, here is the code below:
Format(MthInput, "mmmm")
however, whatever MthInput value is used say 1,2,3...
the month display will always either be december or january why is this so?
i want it to be 1 -> january
2 -> feburary
:
:
12 -> december
did i use the format function correctly?
I want to format the text using format function. how i format the word apple to "apple" (With Quatation mark).
str = Format(Me.word, xxxx)
Hi,
In my database I have feild for date and the default value of it is taken from know function so the date in the feild is showing 22-feb-08 21:55 but I want it to be in dd-mm-yy hh:mm. I tried to used this format in the table but whenever I click on the feild the date shows in 22-feb-08 21:55 format so how I can keep it as dd-mm-yy hh:mm perminent.
HOw to use the format function . I had tried to use it but does not work for me .
when i click properties of the particular column in a query and go to format tab , i type in mm:dd:yy( i want to change the format of date ) and then execute the query but nothing happens.
can someone help me ?
Percentage: Format(CountOfRegular Booking/DCount("*","tblBooking"),"Percent")I get an error: 'Undefined function 'Format'' - how can I fix this? The above expression is supposed to convert values into percentages. I copied the expression from someone's sample database (to help me) and I just changed the appropriate parts around, but I get that error. I put a space between 'Regular Booking' because that's how it is presented as my field name, but I also put them together as one word to see if that would solve matters, but no luck. The sample database works fine, however.Sample query expression:Percentage: Format(CountOfbooleanFieldName/DCount("*","TableName"),"Percent")Thanks guys. :D
View 6 Replies View RelatedDoes anyone know of a function that will change the date format from yyyymmdd to mmddyyyy? I have a linked table to a data repository, I'm using a date field in a query and would like to use the dateadd function to return a certain date range from 7 days prior. I can't get it to run, I thought this might be due to the date format.
here is what i have in the date field as the criteria: <DateAdd("d",-7,Now())
the date field is formated to yyyymmdd
Any suggestions?
I looked for missing references after I had received this message. List of available references had 4 selected items:
1) Visual Basic for Applications;
2) Microsoft Access 15.0 Object Library;
3) Microsoft Office 15.0 access database engine library
4) OLE Automation;
I tried to check off the 3rd and the 4th one, but it came to nought (error didn't dissapear). What else can I do ?
I m using Access 2010.I m Facing a problem in the undermentioned expression,
OPB: 1*Nz(DSum("[Trans]","LogDetail","[LocationID]='" & [LocationID] & "' AND [ProductID]='" & [ProductID] & "' AND [LogDate]<#" & Format([FromDt],"dd-mm-yy") & "#"),0)
when i run the query it gives "Undefined Function 'Format' in Expression" ?
Code:
Private Sub Form_Open(Cancel As Integer)
cboDateStart.Value = Format(Now() - 7, "short date")
...
End Sub
When I run procedure the Format is highlighted and popup error message:
Compile error: Can't find project or library
I upgraded to Access-2010 and the Text File Import function will not recognize dates in the format YYYY-MM-DD. The import dialogue sees enough to recognize the field as a date, but then every date encountered is written to the Import-Errors table. This is true whether the file has a .txt or .csv extension. The actual file format is .csv.
View 6 Replies View RelatedI 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.
So basically I need making a function that will count the number of records from another table/query based on a field from the current query.
View 2 Replies View RelatedI have a project at hand and it's been a predecessor of mine and client has asked me to do some work on it and extend functionality - but I have not really delved into Access before and I have had to worked my way through to this final snag :/
The Main Form has one sub form. This sub form allows the user to add multiple order items i.e. qty, stock, description from records within the system - fairly straight forward.At the last column of each row is the sub total of those particular items i.e.
Qty Unit | Item ID | Total
-----------------------
2 | 1234 | 80.00
------------------------
1 | 43526 | 20.00
------------------------
> | |
So the total is a function of =[Qty Unit] * [Unit Price].Then in the Footer of this SubForm is the Sub Total
=SUM([Qty Unit] * [Unit Price])
All fine and well..... However, the additional functionality kicks in.
Lets add the additional customer_id from the Main Form. Each Item bought is dependent on the customer_id i.e. they get special prices depending on who they are.So a New table is made which has the Item ID and SpecialPriceID (of a table to define as a specialPrice) and the Price linked to this Item and Special Price category. So say that there are two groups of users "wholesale" and "nonwholesale" these would be SP_1 and SP_2 and each client is defined either one of these, and each stock item has a Price for each SP_1 and SP_2. Hopefully I've explained myself there.
Back to the SubForm. So now the Total needs to calculated differently with needed the external customer_id from the Main Form.
Code:
Function CalculateSpecialPrice(ItemID As String, CustomerID As String, Unit As Integer)
Dim SPSelect As String
SPSelect = "SELECT Price FROM [Items_SpecialPrices] WHERE"
SPSelect = SPSelect & " ItemID = '" & ItemID
SPSelect = SPSelect & "' AND SpecialPriceID = (SELECT SpecialPriceID FROM Customers WHERE customer_id = " & CustomerID & ") "
[code]....
its the sub total I just keep on getting #Error on. I have even watched (using alerts) that the correct return variable is the same as the individual rows. This is the equation I used for the SubTotal within the footer.
=SUM(CalculateSpecialPrice([Item ID], [Form]![FormName]![CustomerID], [Qty Unit]))
#Error
I am exporting data from access 2007 to Excel 2007 using VBA code. I have a whole number, which I want displayed as whole number. But after the export, the number is using the 1000's seperator in Excel. So for example if my original number in access table is 12000, it is showing up as 12,000 in the excel file.It has something to do with the NumberFormat property. I have tried doing this but doesn't work.
objSheet.Range("A1:A7").NumberFormat = "General"
We're using Access 2010 on Windows 7.We have a large database design where we send reports as e-mail attachments. We need to change the way the file is automatically created when sent. I goes to snapshot form every time we attach it to an e-mail. How can we get rid of the snapshot .snp file format? How can we change it to pdf or accbd?
View 3 Replies View RelatedWe have a date function that converts a text date format. Can someone help me with time function to do the same thing? We want military time. The field is like this now: txt fields.
160037
213137
224356
235716
235800
12341
21708
22732
Here is the date function we use:
Function f2Date(strDateOld As String)
Dim strDate As String, strMonth As String, strYear As String
strMonth = Mid(strDateOld, 5, 2)
strDate = Right(strDateOld, 2)
strYear = Left(strDateOld, 4)
f2Date = strMonth & "-" & strDate & "-" & strYear
f2Date = CDate(f2Date)
f2Date = Format(f2Date, "mmmm d yyyy")
End Function
Hi all,
I need a little help. In my DB, I have a command button set up (I was tired of typing in dates) for date, but I used the Now function, which also gives me the time.
Now I have over 3000 subrecords of the main ones. I now need to queries transaction for that specific date, but it also retrieves the time.
I tried to go back and change the NOW to DATE in VB, but the code does not run.
How do I change all records that have date and time (using NOW function) and only click that command button to show only the date (mm/dd/yyyy)?
Thanks in advance.
I'm trying to use Conditional Formatting Options on a Text box called "Date". I'd like that this box become Red if its value is between value1 from Text box "Start Date" and value2 from Text box "End Date" or Green otherwise (in this way the color change dynamically every time I change the values of Text Boxes "Start Date " and "End Date"). How can I do that? What I need to type in the Conditional Formatting Windows?
Is there any other way to do that without using Conditional Formatting?
I really forgot how to do this, how can I change the format of my ID, I have set it up as autonumber and I need to view as
For Example
ACA001
ACA002 etc
The way I have it now is just simple plain numbers 1,2,3,4,5..etc
Thanks.
I have tried various suggestions posted, but still can't seem to get the result I need. I have a field I have to import to a database, SS (Social Security Number). In the database I am exporting from, it is text and does not use leading zeros. In the database I am importing into, it must have leading zeros and be in the format 123-45-6789 as a text field. I have tried input masks, formats, etc but still can't seem to make it work. I do not want to add zeros to fields that have the beginning three digits, or add too many zeros to the fields having two of the three digits. Any help would be appreciated.
View 4 Replies View RelatedI have a query where I extract today's date and time using now() but need the extracted file to display the date/time as YYYY-MM-DD HH:MM:SS
View 4 Replies View RelatedI am having difficulties converting a chart/form to from 97 to 2003.
I keep getting "Undifiend function 'Format' in expression."
The query behind the form is
SELECT (Format([Dated],"MMM 'YY")),Count(*) AS [Count] FROM [apps set up] GROUP BY (Year([Dated])*12 + Month([Dated])-1),(Format([Dated],"MMM 'YY"));
Any ideas why?
After I send the file using RTF format, I open the file, the border line cannot be transfer there. But, the report can show borderline. After transferring, the borderline cannot show.
How can I transfer borderline and the image?
Hi,
I've created a database to keep track of schedules, and have created a report that displays like a calendar.
Similar to Pat's example here
http://www.google.com/url?sa=D&q=http://www.access-programmers.co.uk/forums/showpost.php%3Fp%3D288791%26postcount%3D17
What I would like to do now, is create a DAP that will display in a similar format. But because the report uses subreports, it doesn't just tranfer over.
Does anyone have any ideas on how to create a simlar calendar format, within a DAP?
We want to use this on our intranet, so everyone can see each others schedules, and this is the only thing left I have to do. I just can't seem to get anything to work.
thanks
Chris.
I have a problem with one of my combo box. The format setting of the combo box doesn't change anything to the way I see my data. The row source is based on a query of which the 'data sheet view' shows my field with three decimal number. Whatever setting I change on my combo boxe parameter, the data in the list are always shown with 2 decimals. What can i do to view my data with 3 decimals?
View 13 Replies View Related