Select Case

[ChiTownDiva]

Happy Monday All...

I have a question...

I want to import some data using an import spec (no problem), but sometimes the data will have one extra column that would need to be excluded depending on the customer (no problem there either--I have an extra import spec to take care of that). What I want to do is write some code to select the appropriate import spec depending upon the number of columns there are in the txt file. I know I need to use an If statement and/or Select Case, but I have no clue how to use the two together.

Here's what I have so far:

Function GetValue()

DoCmd.TransferText acImportFixed, "VALUE Import Specification", "VALUE", "C:\Documents and Settings\My Documents\Linkage\" & Forms![frmFilePath]![txtChannel] & "\" & Forms![frmFilePath]![txtStore] & "\Imports\VALUE.txt", False, ""

End Function


Is there a way to do that?

Thanks in advance

ChiTownDiva

 

[earthandfire]

As its a fixed with format - get the filesize into a variable (eg fsize)

If fsize Mod FixedWidthSizeForOneSpec = 0 Then
UseThatSpec
Else
UseOtherSpec
End If

Hope this helps.

 

[ChiTownDiva]

Thanks earthandfire...

Maybe I'm an idiot (and that's not out of the realm of possibility ), but I'm not understanding what "fsize Mod FixedWidthSizeForOneSpec" is supposed to be. Is the whole thing a variable?

Thanks

ChiTownDiva

 

[earthandfire]

fsize is a variable assigned to the filesize

You have two fixedwidth specs, so you should know to total size of each of them so

FixedWidthSizeForOneSpec is the size of one of them

The Mod function does an integer divide and returns the remainder

e.g
10 Mod 3 = 1 (3 remainder 1)
10 Mod 5 = 0 (2 remainder 0)

So if The filesize of the data file MOD the size of one of the specs = 0, you know to use that spec, otherwise you need to use the other.

Hope this makes it a bit clearer.