I used the format text box in my query design to generate weekdays as text from my date field. It works great. However, I was hoping to produce some graphs where I could use those weekdays as 7 categories to plot against some other data. Now I am finding that to be a problem as the "real" format is a date and when I try to change the format in excel it creates this random number. Any suggestions on how to get around this problem. Thanks a bunch.
i want to dispaly the weekday or the day of the month
i am using the formula =format([date],"d") for day or "ddd" for weekday in the text box but it is not returning the value that i want. returns #name// whats wrong with the formula
Hello, I have a Field in a Table called Fund Codes and they are entered as text: 1, 2, 3, 4 all the way to 1000. Now i need to find away to change 1 to 001, 2 to 002, 3 to 003, 10 to 010, but leave 100 as 100 and 222 as 222, so only add 0s to numbers from 1 to 99. Is there a way to do it? I know i can change the text to numbers and use format to do that, but i need to leave it as text. Please help. Many thanks!
I have a table with the following fields: Tool Text Tool_ID Text Description Text
The Tool_ID field holds a number if the tool provides a tool id. However, if the tool doesn't provide a tool id, the text "No tool ID given" is automatically placed into the field. Therefore, it needs to remain a text field for this reason and others that are too long to go into. Anyway, I created a query that pulls only one specific tool which always provides a tool ID. Therefore, the results of my query will always have a number in the text field "Tool_ID".
Question: Is there a way to convert the text field to a number field within the query?
But this has not worked. (If someone could explain the significance of the numbers in the function that would be helpful also - Access help did not provide this information)
Hi, I get the user input from a text box in a form, then i use it in the query.
in my query i used the DateDiff function in the expression. should I convert the data type from string (the user input from the text box) to date type first before using the DateDiff function?
in the SQL view, I wrote the following, but it says incorrect:
SELECT availability.machineName, (DateDiff("d",CDate(Forms!frmMain!txtStartDate),CDate(Forms!frm Main!txtEndDate))+1)*24 AS totalMonthlyHours, availability.type FROM availability WHERE (availability.date)>=CDate(Forms!frmMain!txtStartDate) And (availability.date)<=CDate(forms!frmMain!txtEndDate) GROUP BY availability.machineName, availability.type;
Im trying to construct a query which returns a field that has 21 different types of values.
Is there a way to assign a text value to each numeric value when the results are displayed?
Eg: 21 = "Local Sales", 22 = "National Sales", 23 = "International Sales".. and so on.
Ive looked at the Choose and IIF functions but I cant seem to work it out
..............
Anouther little problem, which should be basic. I want to exclude certain records with values such as 31 and 41 and 42.
When i type <> 31 or <> 41 <> 42 in the criteria they are all still displayed. Although when I type one of those criteria by themselves they ARE EXCLUDED. Is there something dumb I am missing here??
how to convert the value of option box ( 1, 2, 3, 4 ) into text.In my database, I want my field to contain "Permanent", "Seasonal", " Project", "Probationary" rather than 1, 2, 3, 4. How to get it in text ?
I have a field that is a combo box that I would like to convert into a text box. When I try to change the 'display control' to 'text' I loose all the information that is displayed in the field. I want to keep the current values that are there now, just do away with the combo box.
I have tried simple things like copy and paste, but that just makes the new field a combo box as well.
I have a database with a query that returns a list of printers. This is created by finding all the printers in the database that take a perticular cartridge by code. Sample below.
PrinterName ----------------- Some Printer 1 Some Printer 2 Some Printer 3 ETC
What I want to do is take the results from the query and convert it into a single line of text and place it into a column of a table related to that cartridge code.
So if say the cartridge code '12345' returns a list of printers such as;
printer 1 printer 2 printer 3
I want to take this data and turn it into a line like this;
printer 1, printer 2, printer 3
I then want to take this new line and place it in a column called Desc in another table relative to the original cartridge code.
table should look like this after query. BEFORE: CartCode | CartDesc | Price | Desc 12345 | Something here | 10.00 |
I want to have a question in my query that does not get answered by date but by a concatenated field named "MonthYearPeriod".
We always forget how many days in a month. But we remember the month and year. So for say a Sales Range of Nov 2006 to Oct 2007, we prefer to respond to the criteria question as: 11-2006 and then 10-2007.
FiscalMonth and FiscalYear are both Long Integer number fields in the table.
Query field concatenated: MonthYearPeriod: [FiscalMonth] & "-" & [FiscalYear]
criteria line: Between [Month-Year START DATE] And [Month-Year END DATE]
However, i get other crazy time periods. So this is not working. Maybe on the Query field box, I need extra coding to convert the number to text?
I assume that a Dash sign between the month and year are fine. Thanks
I have a report where I need to display a box with the following:
="text text text " & url &" text text text"
I need the URL to be bolded, but not underlined or blue. The URL is a variable, so I'm building the text using a string. It looks like the only way to set part of the field to bold is to make it rich text and add html bolding instructions, but that converts the url to a hyperlink. I don't want it to be a hyperlink, just text.
I have an ms access Database(2013 version). There are about 10000 records. There are some columns with field property of "short text" but contains the values like that 0.4,7, 9.0 etc I would like to convert the "short text" into "double" without loosing information.
I am designing a database for our audit teams to carry out reviews of our claims handlers and have put next to each of the questions on a form an option group of three option buttons, "Yes", "No", "Not Applicable" with the values "1", "-1" and "0" respectively. This value is then stored in a table under the relevant field, intQuestionValue, for Q1, Q2, Q3 etc.
This works well for calculating scores, percentages etc. for all the questions. However the manager of the team has said that there will be times when he wants to report by the number of "No" answers for example, so that he look at trends and build these into training plans etc.
I do have a table, tblScores, which has two columns in it. One for the text element, "Yes", "No", "N/A" and the other has the corresponding numeric value in it.
Because there are numerous questions i cannot seem to create a join in a query for the numeric value to bring back the text value. The only other method that came to mind was to write the text outcome to a table as well at the same time, but I'm not sure how I would do this at the same time as clicking the option button, or would it all save when i (the user) closes the form?
I had to delete a database so I exported selected records using export as a text file thinking I would get some sort of delimited text file that I could later use. Instead I got some sort of fixed length file with .............. between records and | between fields.
I'd like to convert this data to a tab delimited file.
I use a memo field in a form. This memo field is used to store "steps" for doing something.
To keep things neat, I use ENTER between the steps so there is a blank line between each. This looks good in the form, however... I export this information (from the table) to a CSV/text file to use online. My online script requires that each record occupy only one line. But, the exported file has separated the "steps" into multiple lines just as it appears on the form.
I would like to use the HTML tag <BR> to separate the line feeds in the CSV file. I can enter these in the FORM instead of pressing ENTER, but then the form looks bad - harder to read.
I have a linked database that stores date values as YYMM in text format. I have no control over how that information is kept. I cant seem to find a way to convert YYMM text to date format. Additionally I would like to add the last day of the respective month to that data.
