I want my PRODID (PK, AutoNumber) field to have "PROD-" before the number, i thought it could be done by seting format to "PROD-"# but that only displays it inside MS A, when i use sql queries through php it only retreives the number. How can i avheve this?
View RepliesWell I have been searching but have not exactly found the answer I need so:
I have a linked table in my database. One of the fields on the source table is a date field but the data type is Text. So there is data in the field that represents dates but they are formatted as text like this:
090506
101106
120506
I need the field data to behave as a date, any ideas? I can't change the source data type.
Then...at some point I want to produce a query that shows all records with a date earlier than the current days date...in pointers in the right direction on that will be appreciated also.
Thanks!
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?
Have a continuous form with 2 date fields in each record .
If dateField1 is empty ,Then I want a way to make DateField2 Take its Place .
I know how to make things happen with conditional formatting on one field by UsingIsNull ,I have tried sending datefield2 to back.But cant work out how to bring it to front If DateField1 IsNull.
I have a string "HICX106-StandardTop-Venderedstest", I only want the first 30 letters or numbers ie "HICX106-StandardTop-Vendereds". Can anyone help, guess this is really easy!!!!!!!!!!
View 4 Replies View Relatedhello, Not sure if this is right placeto post.
In the subreport, I calculated a difference between budget and actual. I want to present this difference in a way that if it is positive, then just show it the way it is, but if it is a negative number, I want to show it in a (). FOr example, if it is -123, I want to show it as (123).
Can anybody help me with it?
Thank you.
I'm using an input mask of:
AAA-AAAaaa
This allows three Characters, followed by a "-" followed by three more characters, followed by optional three more characters.
My problem is that the "-" is not actually part of the data. It is only there for viewing. I would like (need) it part of the actual data becuase I use this information else where (VBA, exports to excel, etc...).
How can I define a field that required the hyphen as part of the real data.
I'm posting this in both VBA and Tables because I don't know if the answer is via a mask or code. Either way is fine
Thank you
Hi,
I have this code which makes all the data displayed on the form in this respective field capital letters e.g John will be displayed as JOHN and saved as so.
However, what i want to do is only make the first letter as John not JOHN. How can I change the code?
Me.surname_forename = UCase(Me.surname_forename)
Thanks
I have changed the inputmask of a field based on an if statement. however i put / characters in place and it wont leave them there can anyone help.
Me.txtbox = "AAAAA/AAA/AA"
Does Access able to extract and format data from Access such as follow:
abc/d e4.5% 0216 010203 USD22.35 - extract 'abc', '010203', '22.35'.
abc - it's always in front, it's either 'abc' or 'wxyz'. If 'abc' is not shown, just input 'wxyz'.
'010203' - it will be in the middle, it may appear as abc010203 or abc222010203, always in 6 digit.
22.35 - always at the back, after three alphabet, if it's not shown, just input 0.
I have been able to do it in Excel, but I am wondering if it can be done in Access so that I can eliminate the process to import the text file into Excel.
Please advise and thanks.
May i reach following function by Grid Query design or by SQL
Original:
==============================================
Factory 1-----------Product A
Factory 1-----------Product B
Factory 1-----------Product C
Factory 1-----------Product D
Factory 2-----------Product D
Factory 2-----------Product E
Factory 2-----------Product F
Factory 3-----------Product D
Factory 3-----------Product E
Factory 3-----------Product F
Result:
==============================================
Factory 1-----------Product A,Product B,Product C,Product D
Factory 2-----------Product D,Product E,Product F
Factory 3-----------Product D,Product E,Product F
I have an old table that has many, many records. One of the columns lists the Date of Birth. Here's my problem:
Users have entered data into this column as "71462" This representing July 14, 1962. Is there a way to automatically format all data in this column so that it is more palatable - e.g. 07/14/1962?
Changing the column Data Type to "Short Date" in Design View would delete all existing data in that column, so this is not an option.
Thanks for your help.
I have a query which is pulling its data from a form, which in turn is pulling data from a table. When I select 0.82 on my form, my query runs fine. But when I select 0.826856 from my form the query does not return any result.
0.826856 is available in the table and the pull down list in the form. The data format for all my table, form and query is General and decimal places is set to 8.
Could you plz tell me why I am not able to run the query using 0.826856 ????
I am getting the following from a query
date|id1_|id2___|
1___|nick|james|
2___|john|nick_|
How can i convert this data this
id_____|date1|date2|
nick___|_1___|_2___|
john___|_1___|_____|
james_|_____|_2___|
Best Regards
I am trying to format my Number data type to look like this: 0001 / 2013 where "0000" is the prefix, 1 is the number and " / 2013" is the suffix.
Tablename: tblclients
Field Name: DelNoteNo
Data type: Number
Form
Control Name: DelNoteNo
Control Source: DelNoteNo
I am searching on this forum but I can't get it working.On the Before Up date procedure i have:
DelNoteNo = Nz(DMax(DelNoteNo, "tblClients"), 0) +1
but its is giving "invalid use of Null "
I have a database where I need to import data from an excel file.
What my employer needs me to do is upon appending the data to a table which I named properties, under the field which I called owner, all the names should follow the correct format. Most of the inputted data does not follow the correct format.
The correct format of the names should be:
K. CURTIZ -> Where K is the initial of the First Name, it should have the '.' and a space before the Family Name
Here's an example of a data that is inputted incorrectly:
R OTTESON -> it should be R. OTTESON
another example where there are 2 names of 2 different persons, it should be inputted this way:
A. & J. PANAG
Mostly inputted incorrectly this way:
J&T BOTIFAN -> it should be J. & T. BOTIFAN
I saw a thread once that explained how to format a date or phone number field after you've entered the data, but I can't find it again.You do it so that you don't have the __/__/__ or the ___-___-____ pop up in the field and don't have to worry about inadvertently starting typing the data in the middle of the field instead of the beginning.Anybody know where that thread is at, or know how to do that?
View 2 Replies View RelatedIs there a way, either in a form or report, to modify the format of a text string within a field?
Basically, we are gathering text data using a memo field (due to the amount of data) and would like to highlight sections of the text by making it bold. It does appear that in Access a field is all or nothing in terms of formatting.
Can one put characters that would identify the start or end of such a string in the data itself?
Thanks.
I have a calc'd percentage field in my query. This is a multi user app and the problem is that is can (and does often) happen that the numerator information for my calc gets entered before the denominator data and therefore the calc'd field shows and as an error.
I attempted to use a simple IIF statement to input a message rater than the error code but now the field will not format to Percent. It worked, I got the message to display and it calc'd the records that had data, it just will not display in percent format. It does let me set the format to percent, it just doesn't display and does not allow the decimal places to be set.
This was the line:
CalcPercent:[Numerator]/[Denominator]
This is the new line:
NewCalcPercent:IIf([Denominator] = 0, "Message", [Numerator]/[ Denominator])
I've tried to search for an answer and I'm sure it is going to be something simple that I've overlooked.
Any thoughts?:confused:
I have two tables that I import that have both a date field and a time field formated as text. date field looks like yyyymmdd and time looks like hhmmss. I am using two append queries that combine the data into one table and I need the date and time fields to be date and time format respectively. When I run the query as is I get no values in either the date or the time field due to a data tyoe mismatch. My question is how do I convert the input table date and time text type fields to date and tie format for the append query.
Thanks for the help
Tony
Hello. I'm triyng to change every field format in a Form each time I jump from one field to another. I want it to get grey and bold when it got the focus
I managed it with a GetFocus and Lost Focus for each field but whena form has a lot of fields it gets "a lot" of programing.
Is there a way of doing it another way, for instance On current
Thanks
If I'm working strictly from SQL, is there a way to format a field as currency, standard number, etc. without using the format() function? Basically the equivalent of setting the format in the property sheet in design view, without going to design view.
View 1 Replies View RelatedI have a table that contains information along with a field named "Date Input" and then a calculated field called "Due NLT Date". New records are appended to this table daily. The calculated "Due NLT Date" field is simply "[Date Input]+5".
In some unforeseen circumstances the "Due NLT Date" field needs to be extended to 6 or 7 days from the "Date Input". I am trying to find a way adjust the number of days using a command button/VBA getting the desired number of days either from an input box or from a txt box on a form.
I have been playing with using SQL ALTER TABLE in VBA but can't get it to work.
I'd image it would look something like this but how to write it correctly:
RunSQL ALTER TABLE "table" FIELD "field" to calculated "[Date Input]+ & input box "Enter number of days""
I have a button on a form that creates an email and inserts fields from my database. I have a field that is set to Long Date format type, but when it populates the email, it shows as a Short Date format type. Is there anyway to retain the Long Date format?
View 1 Replies View RelatedHi,
How do I format data in my report to display with a dollar sign?
I am using the following format $#,##0.00;($#,##0.00), but somehow I get a Format$ paramater to enter value.
Thanks
df
Hello,
Is it possible to pass a AutoNumber Format variable and data to a new table using an Append Query? If not does anyone know what I can try?
Thanks!