Search results

Thanks, Thats great. It has the same function as NULLIFF in transact sql. I also found that if I changed the null to 1 then I get a 100% increase if it increases from 0 to 1 for example. SELECT ((fieldA - fieldB )/ iif(fieldB = 0, "1" , fieldB)) * 100 AS percentdiff FROM mytable;

Hi I have a simple query below to find the percentage difference between 2 long integer columns. However I find that if any of the 2 values in a row are both zero it causes an error in the result for this record and if I run it from a VBA module it causes an overflow error and stops the routine...

Thanks. I should have mentioned it was a make table query I wanted to run. So I have decided to go with running the query first and renaming as user defined after, it seems simpler. Option Compare Database Private Sub import_Click() On Error GoTo Err_Import_File_Click Dim...

Hi I import a table into access using transfer text with the code below. The code asks the user for the name of the table at runtime. I then want to run a query on the table with the same code but how can I do this if the table name is defined by the user. Thanks Option Compare...

hi I have a table with a column [countries] ie England, Scotland, Wales. I was trying to write SQL to make a new column with just a code for the country. So all occurences of England would be 1, Scotland would be 2 etc. I suppose I could duplicate the column and run an update query. But...

Hi I wonder if anyone can help me. I have a bunch of CSV text files (over 400) and want to add 2 leading spaces to the beginning of each row. So the rows would go from this to this. (quotation marks are only for format purposes)...

Yes, thanks this helps. I also found out that date serial works. DateSerial(Right([Field3],4),7,1) Cheers

Hi! Help needed please. I have a date field which look like this: test1 Field3 00/00/1939 00/00/1993 00/00/1966 00/09/1997 00/00/1976 00/03/1998 00/00/1991 What I am attempting to do is to update all the days and months to 01/07 but leaving the year intact. I have thought of the...

Hi yes its supposed to open a different file on each record. the code at the moment is thanks

Thanks I thought it might just be adding a directory tree somewhere. I don't want to do anything with the list of numbers, just for them to pop-up when you click the command button. However I would like the pop-up list to be specific to the particular record you are looking at, I can't...

Hi I have a form where I have added a command button which opens a saved notepad file when you click it. However the command button performs the same action whichever record of the form you are on. What I want is a command button specific to only one record of a form, not the same across...

Hi I can open notepad application with the above code but what the code for opening a specific saved notebook file with a command button on a form. The notebook file is saved on c:\ for example. Or.... It is just a list of numbers I want to open in notepad is there a nicer way to open a...

Thanks for the advice. I'll try that

Hi thanks for your reply. I am importing to many tables as each file may contain duplicate id's and I need each record to have a unique id. The only way would be to import to one file with a new field with the relevant file name giving it unique status. I think this can be done using a...

Hi I'm using the transfer text method to import multiple csv files, 10 or more. DoCmd.TransferText acImportFixed, "Import_Specification", "table_name", strFolderPath & objF1.Name, False as it is it only appends to one table "table_name" , so how can I import each file to a seperate...

Thanks format worked great Expr1: Format([Field1],"00000") didn't check the other one yet. Cheers.

Hi how can I write an update query which adds a '0' to the beginning of a text string in a column only if the text string has 4 characters. If the string is 5 characters I don't want the '0' added. Thanks

If you convert the columns to hex using the code below you can run a match query on the two hex columns. Add the following code to a module Function StrToHex(S As Variant) As Variant ' ' Converts a string to a series of hexadecimal digits. ' For example, StrToHex(Chr(9) & "A~") returns...

Thanks for your reply. I think you are right that this is too much just for a count. What I want to do in the future is to find a way of running the type of sql queries access can without importing files that are too big.. This may be looping through each one or amalgamating each file into...

Thanks Here are a couple of text files with sample data. They are in fixed width format. The data looks like this: 0001197201011001400078R000000000000000032...120000I 0001199201011001400256R00000000000000005853.110000I 0001199212211066000000R19931221199212218B314000000I...