I have the following VBA code to search for a string
Code: 'Search by Phone Private Sub CmdSearchPhones_Click() Me.Refresh strSQL = "SELECT Tbl_Contacts.ContactID, Tbl_Contacts.FName, Tbl_Contacts.LName, Tbl_Contacts.Address, Tbl_Contacts.City, Tbl_Contacts.State, Tbl_Contacts.Zip, Tbl_Contacts.HomePhone, Tbl_Contacts.WorkPhone,
[Code] .....
My issue is that this works 50/50, because phone numbers on the database are NOW being stored in this format (000) 000-0000 and previous DB phone numbers got stored as 0000000000. The above code finds previous DB phone numbers (even if we dont enter all 10 digits) without a problem but has issues with new phone numbers stored in the new format UNLESS I specify the search with (000) 000-0000 (but we want to be able to search without adding that and without typing all 10 digits - in case we forget part of the number we are looking for)
How can I tell my query to find both formats, with the (000) 000-0000 AND 0000000000 ....
I have a mail merge from Access into Word that works great except for a baffling problem with phone number formatting. Instead of (987) 654-3210, on certain records the merge produces 9876543210. I cannot identify a pattern to explain it, but it is not random. Certain records consistently leave the format behind on the phone number, some on the fax number, and some on both. This problem is affecting about 1/3 of the contact records. I have the Input Mask on Phone and Fax in the table set to !(999") "000-0000;0;_. Is anyone familiar with this problem?
I have an inherited database with years worth of julian dates stored as numbers (e.g. days 1-366 for a leap year). I also have some fields stored as short dates. I would like to run a series of parameterized queries on this data, some using the short data and some using the "julian dates" that are actually just numbers. I have the user enter the start and end date in short date format and would like that to be converted and held in an unbound field as a number value. So far I can get the date to appear as a "julian date" (e.g. 1/1/08 appears as 1, obviously the underlying data is still 1/1/08) How do I take that 1 and convert it to number value "1"? Thanks in advance.
I have been trying to convert string into double number format. I am running a SQL query in VBA that returns a double number format; however my understanding with SQL queries in VBA is that they return string only. The results are showing up perfectly fine when I run the query in the query editor; however when I try using the returned value in further calculations in VBA I keep getting a "Type Mismatch" error.
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 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 need to convert the date format October 10th, 2013 to 10/01/2013 in a field using sql in access 2010,I know it has to be an update query but dont know how to start writing the query.
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 different product groups and they can have one of 3 rental rates(5,4,3%) I have written a nested query but I keep getting parenthesis errors, the last one I got told me I must enclose iif functions in parentheses. I know its probably a small thing but I just cant see it. By the way the server is German so ; need to be used instead of ,.
IIf([view_India]![Sub_Group] In (1730;1810;2870);5;iif Left([view_India]![Sub_Group];2) In (01;02;21;24;29) ;5;iif([view_India]![Sub_Group] In (3310;3330;3340;3360);3;4))))
I have a multivalue text field called "Groups" that is generated through a relationship with another table called "Comments". When I set this up I left the field as a text field when it should have been a number field, and now I can't use the database with sharepoint, because the field needs to be a number field.
My Question is there a way to convert it without loosing all my data, as there are over 5000 records?
My Table with the multivalue field is called "Contacts", the field is called "Groups".
I tried update and append queries but I must be doing something wrong....
Hello, I have a text file I'm querying that stores a field as a text. ComponentID:0000000242
I'm inserting this data into a table that stores this value as a number. Thus truncating the leading zeros. ComponentID:242
I would like to create a query on the table using an InnerJoin on the text file column:ComponentID:0000000242 and the table column:ComponentID:242 as the unique identifier. Doing this join gives a case error because of the different data types.
Can format ComponentID using a query so it reads from my table as 0000000242 and do an InnerJoin to the text file. In vb String= Format(242, "0000000000") But I'm finding it difficult to get the query to format this correctly.
Unfortunately, i'm unable to change the column data type in the table structure and am looking for a work around. Is this even possible? Any ideas?
I have a query which gets information from 2x tables where the I'd on one table is the reference number on the second table.I would like to know how I can remove the duplicates on my reference number field?
I use an append query to insert this new record into the table EVENTS_MAIN.
I wish to set the start time and end time for all event days in hours and minutes. I will use these entries for displaying start time and end time for each event days, to calculate daily total hours and event total hours later on.
Therefore, I wish to create a query that will display event date 1, event date 2 and event date 3, based on the number of the EVENT_total_days field, so I can set the start time and end time for each day separately.
Is it possible to "convert" or "switch" the number of the days into number of records? Can a query convert a field value into number of records?