Convert text and numeric in query Results

Hi,

I have a query the appends an access database from an excel spreadsheet (via a link in Access) taken from another program. My problem is that one of the excel columns contains both text and numeric data and I keep getting a 'data mismatch'. I would like to convert both to text I have tried a number of options
exp2:csng([Zip Code])
exp2:Str([Zip Code])
none seem to work.
Does anyone have an idea?

Thanks
Peter.

i am querying data in MS access from an external ODBC database, unfortunately two "numeric" fields i need to link to filter data have got different properties, one is set as numeric the other is set as text and i cannot amend the properties of either. i had managed to overcome this problem by converting the "text" version using "MACRUNMODE:Nz([COSTMACH]![MACRUNMODE],0)".
although this works fine within Access the problem comes when i try to link to this Query using Excel MS Query , it says it cannot connect because excel doesnt recognise the function "Nz", conversly Access doesnt recognise Excels "N" function...... any suggestions how i overcome this

Hi
I am working on very large scale text and numeric data. for e.g in one table i have 9 fields but in 21 million records. I want to know what should i do for obtain better efficiency on queries on the such huge tables. for example make new table query or update query...
The next question is that i want to export the 21 million records in a text file. converting by ms access is very very lengthy and finally i could not open the file. if i write a VBA code for adding records in a text file, that would be faster and safe as well? what's your idea?
I work on Access 2003 on XP platform SP3.

Does anyone know how I can implement the following code into my existing "ProperCase" module so that it would work as one. The ProperCase code I got from these forums works great and has an "Exception List", but the only downfall is it can't detect numeric values before an Alpha character.

Any help would be greatly appreciated as I can't seem to figure out how to integrate the 2 together.

This code checks to see if there is an Alpha character after a number. (Example: 418c centre street) and would then convert it to upper case (418C Centre Street):

	Code:
	Function NumericAddress(strIn As String) As String
Dim arrString() As String
Dim j As Integer
Dim I As Integer
strIn = StrConv(strIn, vbProperCase)
arrString = Split(strIn, " ")
For j = 0 To UBound(arrString)
    For I = 1 To Len(arrString(j))
        If IsNumeric(Mid(arrString(j), I, 1)) Then
            arrString(j) = StrConv(arrString(j), vbUpperCase)
            Exit For
        End If
    Next I
Next j
NumericAddress = Join(arrString(), " ")
End Function

This is the current code I use for ProperCase with an Exception List:

	Code:
	Option Compare Binary
' So that MacDonald  Macdonald in this module only!  The mdlGetException module is set to Option Compare Database
' so that exceptions need only be typed into the exception table once.
Option Explicit                ' Require variable declaration

Global Const mstrTableName = "Exceptions"  'Exception table and field names.  These may be changed if needed.
Global Const mstrFieldName1 = "[Exception List]"   'Holds the exception word.  Case doesn't matter.
Global Const mstrFieldName2 = "Replacement"     'Holds the replacement for the exception.  Capitalize this the way you want
                                                                          'it to appear.
Global Const vbUpperCase = 1  'Converts the string to uppercase characters.
Global Const vbLowerCase = 2  'Converts the string to lowercase characters.
Global Const vbProperCase = 3     'Converts the first letter of every word in string to uppercase.
Global Const vbSentenceCase = 4     'Converts the first letter of the string to uppercase, all others to lowercase.

'Message box types
Global Const MB_OKCANCEL = &H1
Global Const MB_ABORTRETRYIGNORE = &H2
Global Const MB_YESNOCANCEL = &H3
Global Const MB_YESNO = &H4
Global Const MB_RETRYCANCEL = &H5

'Message box icons
Global Const MB_ICONSTOP = &H10
Global Const MB_ICONQUESTION = &H20
Global Const MB_ICONEXCLAMATION = &H30
Global Const MB_ICONINFORMATION = &H40

'Message box default buttons
Global Const MB_DEFBUTTON1 = &H0
Global Const MB_DEFBUTTON2 = &H100
Global Const MB_DEFBUTTON3 = &H200

'Message box return values
Global Const MB_OK = 1
Global Const MB_CANCEL = 2
Global Const MB_ABORT = 3
Global Const MB_RETRY = 4
Global Const MB_IGNORE = 5
Global Const MB_YES = 6
Global Const MB_NO = 7

Public dbs As Database, rst As Recordset
Dim varImpWord As Variant, varPropWord As Variant, varOutput As Variant, varTemp As Variant
'                ^--Improper word            ^--Proper word              ^--Output string
Dim intResult As Integer
Public varSepChar As Variant, varWordList() As Variant, intSepCharCnt As Integer, intWordCount As Integer
Dim booCallFromPropMan As Boolean
Function ProperCase(varPropCaseInput As Variant, intConversion As Integer) As Variant
' This function takes a variant and applies word caps. It also has an exception list stored as an Access table where
' it overrides the word caps rule. Individual cases will have to be added to the exception list manually or at run time by
using the
' ProperManager wrapper function. The exception list cannot contain wildcards; however, capitalization of the exception
(first
' column) isn't important.  The replacement (second column) must be capitalized the way you want it to appear.  The table
' contains two indexed columns of type text and length 50. Table name and column names are given in the constants above.
' The return value is a variant containing the string in Proper Case.
Dim I As Integer
                        
        If IsNull(varPropCaseInput) Then GoTo ProperCase_Exit
                        varOutput = Null        'Initializes variables
                        varImpWord = Null
                        varPropWord = Null
        Select Case intConversion
                Case Is = vbProperCase
                        ParseWords (varPropCaseInput)
                        For I = 0 To Len(varPropCaseInput)      'Iterates through word list
                                If IsEmpty(varWordList(I, 0)) Then      'Word element is empty
                                        If IsEmpty(varWordList(I, 1)) Then      'Word separator element is empty - completed
proper casing.
                                                ProperCase = BuildOutput(varPropCaseInput)      'Build output string
                                                Exit For
                                        End If
                                Else    'Word element is not empty
                                        varImpWord = varWordList(I, 0)      'Get word
                                        varTemp = GetException(varImpWord)      'Check word against exception list
                                                If Len(varTemp)  0 Then   'Word is present in the exception list
                                                        varPropWord = varTemp       'Get replacement word for exception
                                                Else    'Word is not in the exception list
                                                        varPropWord = UCase(Left(varImpWord, 1)) & LCase(Mid(varImpWord, 2)) 
'Proper case word
                                                        If booCallFromPropMan = True Then ManageException
                                                        'Goes to exception management sub if ProperCase was called from
ProperManager
                                                End If
                                        varWordList(I, 0) = varPropWord     'Inserts the proper cased word back into the word
list
                                End If
                        Next I
                Case Is = vbUpperCase
                        varOutput = UCase(varPropCaseInput)
                Case Is = vbLowerCase
                        varOutput = LCase(varPropCaseInput)
                Case Is = vbSentenceCase
                        ParseWords (varPropCaseInput)
                        For I = 0 To Len(varPropCaseInput)      'Iterates through word list
                                If IsEmpty(varWordList(I, 0)) Then      'Word element is empty
                                        If IsEmpty(varWordList(I, 1)) Then      'Word separator element is empty - completed
proper casing.
                                                ProperCase = BuildOutput(varPropCaseInput)      'Build output string
                                                Exit For
                                        End If
                                Else    'Word element is not empty
                                        varImpWord = varWordList(I, 0)      'Get word
                                        varTemp = GetException(varImpWord)      'Check word against exception list
                                                If Len(varTemp)  0 Then   'Word is present in the exception list
                                                        varPropWord = varTemp       'Get replacement word for exception
                                                Else    'Word is not in the exception list
                                                        If varWordList(I, 2) = 1 Then
                                                                varPropWord = UCase(Left(varImpWord, 1)) &
LCase(Mid(varImpWord, 2))
                                                                'If this is the first word in the string, then proper case
word
                                                        Else
                                                                varPropWord = LCase(varImpWord)     'Convert word to lower
case
                                                        End If
                                                        If booCallFromPropMan = True Then ManageException
                                                        'Goes to exception management sub if ProperCase was called from
ProperManager
                                                End If
                                        varWordList(I, 0) = varPropWord     'Inserts the proper cased word back into the word
list
                                End If
                        Next I
        End Select
        ProperCase = varOutput
        booCallFromPropMan = False
        
ProperCase_Exit:
End Function
Function ProperManager(varPropManInput As Variant, intConversion As Integer) As Variant
' This function can be optionally used to *wrap* the ProperCase routine and perform some management of the exception list.
' Deleting items from the list is up to the individual developer.

        booCallFromPropMan = True   'Tells the ProperCase function to call ManageExceptions when needed.
        ProperManager = ProperCase(varPropManInput, intConversion)
        
ProperManager_Exit:
End Function
Sub ManageException()   'Queries the user and adds exceptions to the list

        If varPropWord  varImpWord Then
        ' ProperCase has changed the text. Confirm with user.
                intResult = MsgBox("ProperCase has changed '" & varImpWord & "' to '" & varPropWord & "'. Is this correct?",
_
                MB_YESNO + MB_ICONQUESTION, "ProperCase Exceptions Manager")
                If intResult = MB_NO Then   'Not correct
                        intResult = MsgBox("Do you want to add '" & varImpWord & "' to the exception list?", MB_YESNO + _
                        MB_ICONQUESTION, "ProperCase Exceptions Manager")
                        If intResult = MB_YES Then  'Add to exception list
                                varPropWord = InputBox("How would you like this word capitalized in the future?", ,
varImpWord)
                                'Get the replacement capitalization for this word in the future.
                                Set dbs = DBEngine(0)(0)
                                dbs.Execute "INSERT INTO " & mstrTableName & " (" & mstrFieldName1 & ", " & mstrFieldName2 &
") VALUES (" & "'" & varImpWord & "', '" & varPropWord & "');"
                                'Inserts the exception word into column 1 and the replacement word into column 2 of the
exception table.
                        Else    'Don't add to exception list
                                varPropWord = varImpWord
                        End If
                Else    'Correct - keep changes
                End If
        Else    'No change
        End If

End Sub
Function BuildOutput(varBuildInput As Variant) As Variant       'Builds proper cased output string
Dim ii As Integer

        For ii = 0 To Len(varBuildInput)    'Iterates through proper cased word list
                If IsEmpty(varWordList(ii, 0)) Then     'Element does not contain a word
                        If IsEmpty(varWordList(ii, 1)) Then     'Element does not contain a word separator
                                Exit For    'All words and separators have been found.  Output string is complete.
                        Else
                                varOutput = varOutput & varWordList(ii, 1)      'Concatenate word separator to output string
                        End If
                Else
                        varOutput = varOutput & varWordList(ii, 0)  'Concatenate word to output string
                End If
        Next ii
        
        BuildOutput = varOutput
        
End Function
Public Sub SetSepChars()
'Sets up an array of the accepted word separation characters.  These may be modified by the developer as needed.

        varSepChar = Array(" ", "-", ".", ",", ":", ";", "(", ")", "", "/", "'", Chr(9), Chr(10), Chr(13))
'                                                                                 ^--Tab  ^--{LF}  ^--{CR}
        intSepCharCnt = UBound(varSepChar)    'Counts the number of word separation characters in the array

End Sub
Function ParseWords(ByVal varParseInput As Variant) As Variant
Dim intStartPos As Integer, intEndPos As Integer, intArrayIndex As Integer, varTestChar As Variant, _
intTestPos As Integer, iii As Integer, varWord As Variant

        If IsNull(varParseInput) Then
                GoTo ExitParseWords
        Else
                SetSepChars
                ReDim varWordList(Len(varParseInput), 2) As Variant
                'Declare word list array with as many rows as there are characters in the varParseInput variable (to be
conservative).
                'Column 0 holds the final parsed words, column 1 holds the word separators, and column 2 holds an integer
                'describing the corresponding word's position in the input string (1 for first word, 2 for second word,
etc.).
                intArrayIndex = 0       'Initialize variables
                intStartPos = 1
                intEndPos = 0
                intWordCount = 0
                For intTestPos = 1 To Len(varParseInput) 'Iterate through entire input string
                        varTestChar = Mid(varParseInput, intTestPos, 1)     'Get character to be tested
                        For iii = 0 To intSepCharCnt  'Iterate through word separator characters
                                If varTestChar  varSepChar(iii) Then    'If something other than a word separator
                                        If iii = intSepCharCnt Then   'All word separators have been compared with
                                                varWord = varWord & varTestChar     'Concatenate with previous characters in
the same word
                                                If intTestPos = Len(varParseInput) Then     'Entire string has been tested
                                                        varWordList(intArrayIndex, 0) = varWord     'Insert word into proper
element
                                                        intWordCount = intWordCount + 1     'Advance word counter
                                                        varWordList(intArrayIndex, 2) = intWordCount    'Enumerate word in
array
                                                        intArrayIndex = intArrayIndex + 1   'Advance array index counter
                                                End If
                                        End If
                                Else    'If test character is a word separator
                                        If Not IsNull(varWord) Or intTestPos = Len(varParseInput) Then
                                        'Complete word has been parsed or end of string has been reached
                                                varWordList(intArrayIndex, 0) = varWord     'Insert word into array
                                                intWordCount = intWordCount + 1 'Advance word counter
                                                varWordList(intArrayIndex, 2) = intWordCount    'Enumerate word in array
                                                intArrayIndex = intArrayIndex + 1   'Advance index array counter
                                        End If
                                        varWordList(intArrayIndex, 1) = varTestChar     'Insert word separator into array
                                        intArrayIndex = intArrayIndex + 1   'Advance array index counter
                                        If Not IsNull(varWord) Then varWord = Null      'Re-initialize word variable
                                        Exit For
                                End If
                        Next iii
                Next intTestPos
        End If
        ParseWords = varWordList        'Set output to word list
        'GoSub PrintWordList     'Test function by printing to Debug window - uncomment this line if you want to use
        GoTo ExitParseWords
        
PrintWordList:
        For intArrayIndex = 0 To UBound(varWordList)    'Iterate through word list array
                If Not IsNull(varWordList(intArrayIndex, 0)) Then Debug.Print varWordList(intArrayIndex, 0)
                'Print each element in array to the Debug window
        Next intArrayIndex
        Return
        
ExitParseWords:
End Function
Function GetWord(ByVal varGetInput As Variant, intIndex As Integer) As Variant
'This function will parse a string and return whatever word you like.
'Accepts: input field and index number starting with 1
'Returns: the nth word in the input string.
Dim iv As Integer

        If IsNull(varGetInput) Then
                GetWord = Null
                GoTo ExitGetWord
        Else
                If intIndex < 1 Then    'Can't get a zero word!
                        MsgBox ("Please enter a number greater than or equal to 1.")
                        GoTo ExitGetWord
                Else
                        CountWords (varGetInput)
                        If intIndex - 1 > intWordCount Then     'Index number given is greater than the number of words in
the string.
                                MsgBox ("Please enter a number between 1 and " & intWordCount & ".")
                        Else
                                For iv = 0 To UBound(varWordList)   'Iterates through word list
                                        If varWordList(iv, 2) = intIndex Then   'Column 2 (word count) equals the index
number asked for.
                                                GetWord = varWordList(iv, 0)    'Retrieves the correct word
                                                Exit For
                                        End If
                                Next iv
                        End If
                End If
        End If
        
ExitGetWord:
End Function
Function CountWords(ByVal varCountInput As Variant) As Integer
'Counts the words in a string.

        ParseWords (varCountInput)
        CountWords = intWordCount

End Function
Sub TestParseWords()    'Tests the ParseWords function
Dim strAString As String, v As Integer, intCnt As Integer

        strAString = "Once, I thought I could write a module like this easily; now, I know better."
        
        'Find out how many separated words are present
                intCnt = CountWords(strAString)
                Debug.Print intCnt
        
        'Now call the other function to retrieve each one in turn
        For v = 1 To intCnt
                Debug.Print GetWord(strAString, v)
        Next v

End Sub
Sub TestProperManager()     'Tests the ProperManager function

        Debug.Print ProperManager("GREEN TREES have purPLE LEAVES, MCDONALD.", vbLowerCase)

End Sub
Sub TestProperCase()        'Tests the ProperCase function

        Debug.Print ProperCase("GREEN TREES have purPLE LEAVES, mcdonald.", vbSentenceCase)

End Sub



So I used the last hour or so looking for a similar query to no avail.

Thought I'd throw this to the wolves as I keep stumping myself somewhere around the DMax().

Basically we're converting an old 97 database with very bad keys and no naming system to a newly modeled 2000 db. In our old system we have a table [Table1] with a 3 field key [Year] [Site] [Class]. Because multiple entries of the same class can happen within the same site and the same year this has caused nothing but headaches. In the new model the key is [Year] [Site] [ClassID] where [ClassID] is an incremented byte that (I'm assuming) our data entry people will fill in by hand. The issue is transferring the 20 000 records or so over.

They (being the powers that be) want [classID] to reset to 1 with each [year] and [site].

So what I have gleaned thus far is that I should be able to use DMax()+1 to increment the field however I can't seem to find the correct argument as to just -what- is being DMax'ed. Since this isn't a form but a query that I'll eventually turn into an update I can't reference the control.

Of course the second part of the question is how I'm supposed to tell it to reset the count at each combination of [year] and [site] but I'm willing to sniff around a little longer on that one--would I be wrong to say the answer is to be found in the For... Each... Next???

Thanks,
~Chad

Edit: It should be noted that [year] and [site] are non-sequential numeric fields but [class] is a 3 character text field

Hi I have some data that includes records for each day of the year. The records will change from time to time assuming a new record is reached for a particular date. I would like to be able to do a query using two combo boxes 1)cboMonth 2)cboDay and using these two comboboxes do a query using those values. I do not wish the year to be a factor at all. Because these are records for each day and the year is insignificant in the search.

For example If I choose February in cboMonth and 26 in cboDay I want all records that occurred on February 26 regardless of year. The table that the data is stored in is called tblRecords. It includes an autonumber (key), location, record value and record date. The date is in the 1/1/2012 style format.

I also want to be able to search only by month and have been successful and employing a somewhat cumbersome method of doing so. I have a combo box the same one called cboMonth, a text box (hidden) that converts the text value of month to a numeric month value and a command button that runs a query to search. The query SQL for that follows. SELECT tblRecords.StationName, tblRecords.HighMax, tblRecords.HighMaxDate
FROM tblRecords
WHERE (((Month(tblRecords.HighMaxDate))=[forms]![frmqry]![txtMonthNum]));

Thanks in advance for any help on this. I hope that I did not rattle on too long. I can upload a sample if it will help.

As the title says, i have an identifier field which combines two codes eg. AAAAAA/1234.

I am using a query to extract the number part, however when i try to link to another query it says 'type mismatch'.

I assume that this is because the main query is based on a table where [ShipID] is numeric and the extracted data is based upon a underlying table where [PackageID] is a text field.

Is there any way to get round this?

Many thanks

Hi

New user with probably simple problem!

I'm pulling data out of AS400 using Showcase Strategy with saves the data as a DB IV table. This table is then linked to Access, an append query being used to move the data from the linked DB IV table into an access table for storage and manipulation.

There are 2 fields in the DB IV which contain numeric data. When the DB IV table is opened in Excel the data shows as numbers - no problem. However, when the linked DB IV table is opened in access the numeric data appears as text, the spare field space being full of spaces.

I've tried to convert this text to numeric values using the "VAL" function within the append query but the output rounds each numeric value up or down to zero decimal places. I've set the properties of the destination fields in the access table to standard number with 2 decimal places, but no difference.

Getting very frustrated - any ideas?

Thanks
Richard

I have a datatable with a column of numeric data. The column was originally assigned a type "number" but was converted to "text" by a user.

I need to change back to "number" but I get an error message that says: Quote: Microsoft Access encountered errors while converting the data. The contents of field in 443 record(s) were deleted. Do you want to proceed anyway? I tried adding a new field and running an update query which assigns the value of the original column to the new column using the CInt, Cdbl and Val functions. I get the same result. Some of the values (443 exactly) are not convereted. As these were originally numbers, I do not understand what is happening.

Unfortunately, this is messing up a number of reports and queries which I need to be able to run. Can anyone help me correct this issue? Thanks.

I have a table with 19 fields that gets records automatically added to it daily. The source program that adds the data to the database gets its data from scanning documents and identifies each field to be inserted into the database by a two letter identifier at the beginning of the data required. For example MW250 tells the program that MW250 will be inserted into the field for MW.

When someone wishes to search the database for a specific record, they click on a macro that clears a temporary table of all data, appends all the current data from the main table to this temporary table, removes all the two letter designators from each field and then allows the user to select what is needed to find the records. This is great in all fields except one. In one field I want to be able to search a range of values. I have created a query for this so the user simply types in the minimum and maximum range values on a separate form and then the query will find them. The problem is the query for the range value is looking for a numerical value and while the data in that field is only numerical, it is transferred over as text from the source table which cannot be changed.

Is there any way to do an update or append query to change a field type from text to number after all the other queries are done and have it in the format desired?

One more piece to the puzzle. Not all records will have a value in the MW field. Some may be blank.

At this point I have tried several iterations of CInt but always get the error due to the blank fields.

Thanks for any help on this it is greatly appreciated.

I need to make a query to compare equality of two pieces of data, one is a 6 character fixed-length text string (where the first character can be ignored) and the other is a 4 or 5 digit number (long integer, if 4 digit number can be assumed to be 5 with a leading 0).

I do not have the ability to change the way I receive the data, they are on linked tables of different customers.

I tried using various SQL functions to convert the data in the query but they don't seem to work in Access, so I am getting "Type incorrect in the expression" errors.

How do I do this in ACCESS? I would prefer a SQL query solution to a VBA solution if possible, the data is updated VERY often and the application is already doing too much processing on the form_timer() event.

I am using CDate to convert a date text string into a date value, which is then listed in ascending order in the result.

If I leave the default format as 'dd/mm/yy' the data is in the correct date ascending order when the query runs but if I use -

Format(CDate(etc, etc),"dd mmm yy")

The query is in alpha/numeric order as if the value is a text string and not a date value.

Not entirely sure why it is doing this when all that I have changed/added is the 'Format' function.

Hi,

Scenario:

I'm working on a database which holds numerous data about a number of clients.

After the data has been entered, I want to be able to use parameters and only return information about those client which meet the parameters.

Progress so far:

All the data is currently in a table.

I have made a query which has three different parameters.

I have created an unbound form to gather data (from the user) and I have tied that form to a report. Everything works fine right now.

Problem:

The problem I'm having is that the form is only able to return information about clients when I use one value for a parameter.

For example, if I want the form to return all clients which reside in ABC city, all I have to do is type in ABC in the text box in the form and the form will return all clients which reside in that city. However when I convert the city textbox to a list box which allows multiselect, the form doesnt return any data.

Ideal solution:

I want to make it so that I can select ABC and DEF in the form and have it return all clients which reside in ABC and DEF.

Notes:

I have been trying this for hours now and cant get it...maybe I'm missing something.

Also, I'm somewhat limited using VB codes, everything Ive created so far, Ive done using Access 07 tools provided in the tabs.

Any help is greatly appreciated and thank you in advance for all your efforts.

Alright, I'm at my wits end with this. I can a text file which
contains somewhere in the neighborhhod on 1.6m records. Originally, I
went down the import path, but obviously the import performance was
slow. I ended up going the linked file route which I originally
discounted due to concerns with search/filter performance. Here is the
quandry - I have a simple form connected to the linked table which
allows the user to enter a value into 1 of 2 fields (same table). One
field filters the result is of type text and uses a "like" filter with
wildcards. The results are returned in a matter of seconds. The second
field filters a numeric long integer field - the results are returned
in a minute or two. Everything I know about databases (right or wrong)
says that a filter or query on a numeric field should always
outperform a text search. I've tried converting the search field to
type long using the clng function and converting the numeric table
field to s string using cstr (via a query). Neither field is indexed
since it is linked Any ideas?

I am having major problems trying to sort my report by days of the week. I have converted the date into weekdays but it still trys to sort it by the date (numerically). is this poss. and if so what do I do?

The method I have used to add the day of the week is to add another text box and enter =[date] and then go into properties/format and entered "ddd".
Mon, Tue etc. shows up on the report but when I try to sort by that box it still insists on sorting by the numerical date.

I've also put a line in the query in a similar manor but it won't even sort correctly using that method.

Before I get bald trying, could anyone come to my rescue.

Hello,

I have an Access 2010 VBA/Query problem I’d like to see if the forum can address. Basically, I have two problems:

1. My query runs very slowly (over 2.5 hours to pull 2.5M records) since I have 2 custom VBA functions to aid in calculating date ranges. Yes, it’s a lot of records and its growing!
2. The other being that I cannot filter a reports query when the criterion for the filter is applied to a custom VBA function. I get a “Data type mismatch in criteria expression” error.

I'll do my best to give all the relevant information now, apologies if it’s long, I want to be thorough – it’s probably overkill. The gist of the data: ~2.5M records which track line item detail of order shipments. So, multiple lines make an order while a single line is an item in an order. I need to analyze this data and part of that is determining if orders shipped on time, etc. The table has a number of fields but the relevant ones are:

· ORDER NUMBER
· REQUESTED SHIP DATE – (aka the date the order is received)
· DATE ORDER SHIPPED/RETURNED – RETURNED label is irrelevant
· NEW ITEM FLAG – determines if an item is a 90 day exempt item

From these I calculate (Field name – field values: description & formula):

· STATUS – 5, 25 or 90: 5 days if it shipped in 5 days or if 5 days have yet to elapse, 25 days (“backorder”) if it didn’t ship in 5 days or 5 days have already elapsed and 90 days (“exempt”) if the item has an “N” in NEW ITEM FLAG.
o Status: IIf([NEW ITEM FLAG]="N",90,IIf([DATE ORDER SHIPPED/RETURNED] Is Null,IIf((NETWORKDAYS([REQUESTED SHIP DATE],Now()))>5,25,5),IIf((NETWORKDAYS([REQUESTED SHIP DATE],[DATE ORDER SHIPPED/RETURNED]))>5,25,5)))
· ACTUAL SHIPPING TIME – Positive Integer: uses UDF NETWORKDAYS to calculate shipping time dependent on whether or not the item has shipped.
o Actual Shipping Time: IIf([DATE ORDER SHIPPED/RETURNED] Is Null,NETWORKDAYS([REQUESTED SHIP DATE],Now()),NETWORKDAYS([REQUESTED SHIP DATE],[DATE ORDER SHIPPED/RETURNED]))
· TARGET SHIP DATE – Date: uses UDF NETOWRKDAYS to calculate the day it should’ve shipped accounting for weekends and specific holidays.
o TargetShipDate: TargetShipDate([REQUESTED SHIP DATE],[Status])
· ON TIME – On Time, Late, Pending: analyzes the other calculated fields with DATE ORDER SHIPPED/RETURNED to determine if it was on time, late or pending.
o On Time: IIf(([DATE ORDER SHIPPED/RETURNED] Is Null And Now() dtEndDay. If so, then switch the dates If dtStartDay > dtEndDay Then dtNominalEndDay = dtStartDay dtStartDay = dtEndDay dtEndDay = dtNominalEndDay End If 'Here are how many weeks are between the two dates lngTotalWeeks = DateDiff("w", dtStartDay, dtEndDay) 'Here are the number of weekdays in that total week lngTotalDays = lngTotalWeeks * 5 'Here is the date that is at the end of that many weeks dtNominalEndDay = DateAdd("d", (lngTotalWeeks * 7), dtStartDay) 'Now add the number of weekdays between the nominal end day and the actual end day While dtNominalEndDay 0 Then If Left(strField, 1) "[" Then strField = "[" & strField & "]" End If Do strCriteria = strField & _ " = #" & Format(TSD, "mm/dd/yyyy") & "#" rs.FindFirst strCriteria If Not rs.NoMatch Then TSD = TSD + 1 End If Loop Until rs.NoMatch End If End If Loop Until Not IsWeekend(TSD) If Status > 1 Then Status = Status - 1 TSD = TSD + 1 GoTo TestNextDate End If ExitHere: TargetShipDate = TSD 'Debug.Print TSD Exit Function ErrHandler: ' No matter what the error, just ' return without complaining. ' The worst that could happen is that the code ' includes a holiday as a real day, even if ' it's in the table. Debug.Print "Error: " & Err & " - " & Error Resume ExitHere End Function

	Code:
	Private Function IsWeekend(dtStartDate As Date) As Boolean
'test for weekends
Select Case Weekday(dtStartDate)
   Case vbSaturday, vbSunday
       IsWeekend = True
End Select
End Function


Thank you,

~Chris

Looking for the best approach to updating/altering an Access table via DSN-less connection string.

Situation: This process will be run nightly via MS Schedule tool

Database 1 - contains 1 table which acts as a linked central datasource for numerous databases - this cannot be change. - in some instances this maybe left active which affects the altering of the table. Alter is need to change 3 fields's data types or convert prior to update.

Database 2 - contains the temporary table and the code or possibly just the code to update the data in the linked table source.

Note this code cannot be kept in DATABASE1 - due to the fact that this database is occasionally opened and used by other users, so I cannot have the Autoexec fired when the user access the database - hence the need for Database2.

Now the Oracle data source contains numerous fields that need to be converted in 1 way or another, including converting a date that in contains a text (varchar) datatype ie. 090822, when I attempt to covert this and place in an Access Date/Time field - I am getting an error.

My thought is to create the temp table then export into Database 1 - which will replace the existing with the new data - or possibly have the Database2-tbl linked in Database1 and do a delete and append queries - however, then I run into the conversion issue.

Any and all suggestions is greatly appreciated.

Note the date field = SERVICE_DUE_DATE_CMT, LAST_SERVICE_DATE, PURCHASE_DATE that needs the conversion within the code or using the alter tables

Thanks,

Karen

Here is my existing code.



Public Function FTCSConnection()
Dim sConn As String
Dim oConn As ADODB.Connection
Dim rstOra As ADODB.Recordset, rs As ADODB.Recordset
Dim adoRS As ADODB.Recordset
Dim cn As ADODB.Connection
Dim ctl As Control
Dim j, I As Long
Dim rsField, tblField As String
Dim rsValue, tblValue As String
Dim varNM As Variant ' Nomenclature_Modifier
Dim varLSD As Variant ' Last Service Date
Dim varRNG As Variant ' Range
Dim varSDD As Variant ' Service Due Date Cmt
Dim varEQL As Variant ' Equipment Location
Dim varPAmt As Variant ' PURCHASE_AMT
Dim varPDate As Variant ' PURCHASE_DATE
On Error GoTo FTCSConnection_Error

DoCmd.SetWarnings False

Set cn = CurrentProject.Connection

sConn = _
"Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOC OL=TCP)" & _
"(Host=Server1.com)(Port=1521)))(CONNECT_DATA=(SID =serv1)));" & _
"User Id=*****;Password=******"
Set adoConn = New ADODB.Connection
adoConn.Open sConn
Set adoRS = New ADODB.Recordset
STRSQL = "Alter Table [TL_FTEM_TEMP] alter column SERVICE_DUE_DATE_CMT varchar"
CurrentProject.Connection.Execute STRSQL
STRSQL = "Alter Table [TL_FTEM_TEMP] alter column LAST_SERVICE_DATE varchar"
CurrentProject.Connection.Execute STRSQL
STRSQL = "Delete * from TL_FTEM_TEMP"
CurrentProject.Connection.Execute STRSQL
Set rs = New ADODB.Recordset
STRSQL = "Select * from TL_FTEM_TEMP"
rs.Open STRSQL, cn, adOpenDynamic, adLockOptimistic

STRSQL = " SELECT DISTINCT A.FTEM_ID AS FirstofEquipment_ID, A.EQPT_NAME AS NOMENCLATURE, A.NOMEN_MODIFIER_NAME AS Nomenclature_Modifier," & _
" A.EQPT_LOC_NO, A.SERVICE_ORGN_CODE,COUNT(*) AS CountOfEQUIPMENT_ID," & _
" A.SERVICE_DUE_DATE_CMT, A.LAST_SERVICE_DATE, A.MFR_NAME AS Manufacturer, A.MFR_NO AS Model, A.PART_VENDOR_SERIAL_NO AS VendorPart, A.RANGE," & _
" EM.PURCHASE_AMT, EM.PURCHASE_DATE" & _
" FROM Server1.FTEM_VI_VW AS A INNER JOIN Server1.EQUIPMENT_MANAGEMENT AS EM ON A.FTEM_ID = EM.FTEM_ID" & _
" GROUP BY A.FTEM_ID, A.EQPT_NAME, A.NOMEN_MODIFIER_NAME, A.EQPT_LOC_NO, A.SERVICE_ORGN_CODE," & _
" A.Service_Due_Date_CMT," & _
" A.LAST_SERVICE_DATE, A.MFR_NAME, A.MFR_NO, A.PART_VENDOR_SERIAL_NO, A.RANGE," & _
" EM.PURCHASE_AMT, EM.PURCHASE_DATE" & _
" HAVING (((A.SERVICE_ORGN_CODE) Is Not Null))" & _
" ORDER BY A.FTEM_ID;"
Debug.Print STRSQL
Set adoRS = New ADODB.Recordset
adoRS.Open STRSQL, adoConn, adOpenDynamic, adLockReadOnly
adoRS.MoveFirst
Do Until adoRS.EOF
If Not IsNull(adoRS("Nomenclature_Modifier")) And adoRS("Nomenclature_Modifier") "" Then
varNM = Replace(adoRS("Nomenclature_Modifier"), Chr(34), Chr(34) & Chr(34)) 'double up double quotes
varNM = Replace(adoRS("Nomenclature_Modifier"), "'", "''") 'double up single quotes
End If
If Not IsNull(adoRS("EQPT_LOC_NO")) And adoRS("EQPT_LOC_NO") "" Then
varEQL = Replace(adoRS("EQPT_LOC_NO"), Chr(34), Chr(34) & Chr(34)) 'double up double quotes
varEQL = Replace(adoRS("EQPT_LOC_NO"), "'", "''") 'double up single quotes
End If
If Not IsNull(adoRS("SERVICE_DUE_DATE_CMT")) And adoRS("SERVICE_DUE_DATE_CMT") "" Then
varSDD = Format(DateSerial(Left(adoRS("SERVICE_DUE_DATE_CMT "), 2), Mid(adoRS("SERVICE_DUE_DATE_CMT"), 3, 2), Right(adoRS("SERVICE_DUE_DATE_CMT"), 2)), "Short Date")
Else
varSDD = adoRS("SERVICE_DUE_DATE_CMT")
End If
Debug.Print varSDD
If Not IsNull(adoRS("LAST_SERVICE_DATE")) And adoRS("LAST_SERVICE_DATE") "" Then
varLSD = Format(CDate(adoRS("LAST_SERVICE_DATE")), "Short Date")
Else
varLSD = adoRS("LAST_SERVICE_DATE")
End If
If Nz(adoRS("Range"), "") "" Then
varRNG = Replace(adoRS("Range"), Chr(34), Chr(34) & Chr(34)) 'double up double quotes
varRNG = Replace(adoRS("Range"), "'", "''") 'double up single quotes
varRNG = LTrim(varRNG)
End If
If Not IsNull(adoRS("PURCHASE_AMT")) And adoRS("PURCHASE_AMT") "" Then
varPAmt = Replace(adoRS("PURCHASE_AMT"), Chr(34), Chr(34) & Chr(34)) 'double up double quotes
varPAmt = Replace(adoRS("PURCHASE_AMT"), "'", "''") 'double up single quotes
End If
If Not IsNull(adoRS("PURCHASE_DATE")) And adoRS("PURCHASE_DATE") "" Then
varPDate = Format(CDate(adoRS("PURCHASE_DATE")), "Short Date")
Else
varPDate = adoRS("PURCHASE_DATE")
End If
STRSQL = "INSERT INTO TL_FTEM_Temp (FirstofEquipment_ID, NOMENCLATURE, Nomenclature_Modifier, "
STRSQL = STRSQL & "EQPT_LOC_NO, SERVICE_ORGN_CODE, CountOfEQUIPMENT_ID, "
STRSQL = STRSQL & "SERVICE_DUE_DATE_CMT, LAST_SERVICE_DATE, Manufacturer, Model, "
STRSQL = STRSQL & "VendorPart, RANGE, PURCHASE_AMT, PURCHASE_DATE" & _
STRSQL = STRSQL & "VALUES('"
STRSQL = STRSQL & Nz(adoRS("FirstofEquipment_ID"), "") & "', '" & Nz(adoRS("NOMENCLATURE"), "") & "', '"
STRSQL = STRSQL & varNM & "', '" & varEQL & "', '"
STRSQL = STRSQL & Nz(adoRS("SERVICE_ORGN_CODE"), "") & "', '" & Nz(adoRS("CountOfEQUIPMENT_ID"), "") & "', '"
STRSQL = STRSQL & varSDD & "', '" & varLSD & "', '" & Nz(adoRS("Manufacturer"), "") & "', '"
STRSQL = STRSQL & Nz(adoRS("Model"), "") & "', '" & Nz(adoRS("VendorPart"), "") & "', '" & varRNG & "')"
STRSQL = STRSQL & Nz(adoRS("PURCHASE_AMT"), "") & "', '" & Nz(adoRS("PURCHASE_AMT"), "") & "', '" & varPAmt & "')"
STRSQL = STRSQL & Nz(adoRS("PURCHASE_DATE"), "") & "', '" & Nz(adoRS("PURCHASE_DATE"), "") & "', '" & varPDate & "')"

DoEvents
CurrentProject.Connection.Execute STRSQL, dbFailOnError
adoRS.MoveNext
Loop
rs.Close
Set rs = Nothing
adoRS.Close
Set adoRS = Nothing

STRSQL = "Alter Table [TL_FTEM_TEMP] alter column SERVICE_DUE_DATE_CMT Date"
CurrentProject.Connection.Execute STRSQL
STRSQL = "Alter Table [TL_FTEM_TEMP] alter column LAST_SERVICE_DATE Date"
CurrentProject.Connection.Execute STRSQL
STRSQL = "Alter Table [TL_FTEM_TEMP] alter column PURCHASE_DATE Date"
CurrentProject.Connection.Execute STRSQL
DoCmd.Close acTable, "TL_FTEM_TEMP", acSaveYes
On Error GoTo 0
Exit Function

FTCSConnection_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure FTCSConnection of Module Functions"

End Function

I have an option group on my form now and the user selects the option desired, the value in the option group is then defined as a numeric value.

I now need to put an hidden unbound box on my form that will look at the option group value and convert the value to text to be used for my criteria in my query.

not sure how to write the code for the list box

option group value IIF[Department] =1 then"Shipping =2"Printing"

I have a table labeled tblcatalogs. There is a num field that tracks the accession numbers of a collection of artifacts. duplicate artifacts are given a decimal place.

I.E. item # 10 has two duplicate items labeled 10.01 and 10.02

For items with over 9 duplicates I am having a problem with the ending zero not staying in the numeric field.

IE 12.10 is converted to 12.1 automatically. I would create a mask that places an ending zero in a query however if I ever had 100 duplicates, the ending zeros would convert to .1 and would create a duplicate entry. Is there a way to make access keep the ending zero.

Converting to a text field is not an option because the catalog number will not sort correctly but will sort as

1
10
11
2
20
3
30

Thanks for the assistance

Hi All,

I think the answer to this is no, but I'll ask anyway.

When importing a .csv or .xls into Access, there is one column I import called 'Item Code'. Items in this column can be numeric, alphanumeric or just text.

Obviously ideally I would set the format for this column to 'Text' on import. However, once I have run some queries I export the results back into Excel.

All the 'Item Code' columns that appear in all Excel documents are formatted as 'General'.

Therefore with any Access file I export, the Item Code in Access (text) does not match up with the Item Code in Excel (general).

So my question is - is there any way to maintain a 'General' format when importing into Access that recognises both numbers and alphanumerics in Access, and doesn't need converting into text format?

Thanks for your help.

Nick


Not finding an answer? Try a Google search.