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;
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.
I have a table in access database which contains a text field 'EDate' that stores Date value in format (12-Apr-2013). Now I want to run a sql query on that field. User will give an input date. The sql query needs to fetch me all the records from access database whose Edate is less than or equal to the user input date.
I am using DateValue function to convert my text filed Edate into date. My query is something like this:
select * from table_name where DateValue(EDate)<='user_input_date'
I am able to perform above task if the system language settings are 'English'. But if system language settings are different say Turkish, then the query fails.
I searched a lot on web and found that DateTime function compares test data with the system date time format and gives the result. Thus it fails with different language settings.
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?
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.
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 a query in which I am splitting a long integer into Month, Day, Year and then putting it back together using Date Serial in another query. When I originally set this up, the date was coming through as 5 numbers. Now it is coming through as six. However, Access will error out if I try to set it as a date.
So, how can I redo the fields below to get 2 digits for the month instead of one. The date now comes through as 041706
CInt(Mid([Field1],1,2)) AS iMo, CInt(Mid([Field1],2,2)) AS iDay, CInt(Mid([Field1],4,2)) AS iYear
Looked through DateSerial but possibly not the way to go. I have a text field, "Month", with the dates in this format: Jan-06, Feb-06. What do I use to convert these to date format? 1/1/06, 2/1/06.
Hi all,I'v been at this for awhile nowI did a search and tried out some of the examples but for some reason they didnt work.Heres the format i have it in. D| O | B04 | 30 |1967 all in a text fieldI need to convert it into 4/30/1967I tried creating a new field called DOB and setting it to date/time and then using a concate query but it said it could not convert it.UPDATE Table1 SET Table1.DOB = [D] & "'/" & [O] & "/" & [B];UPDATE Table1 SET Table1.DOB = "#" & [D] & "'/" & [O] & "/" & [B] & "#";Please helpTake care
