Replies

You don’t have to point your combobox at a table/query to collect the options. The wizard allows you so simply type in the options you want & for you this would seem ideal!

Best of Luck

Ian

I suppose a criteria of

nz([Size],0)<1

(assuming size is integer) would catch both null & 0

Good question! The expression looks right so it must be the data in [dtDateChanged]. Try it on another date field.

No answer is ‘wrong’ you just have to decide how you want to run your inventory.

You already said you wanted a FIFO system so that gives you your answers.

If I understand correctly that you only pay your supplier when you sell the item on then anything but a FIFO solution will probably give your supplier problem!!

An old much mentioned favorite is the Find & Replace utilty @ http://www.rickworld.com/

It’s saved me many hours & much heartache

A general solution to this sort of problem are the following 2 functions which return the first part of a string up to a defined character or characters & the part after the character or characters.

Function GetFirstWord(strIn As String, chrDelimit As String) As String
‘ Parameters : strIn – string to search
‘ chrDelimit – character delimiter
‘ if delimiter is not found whole string is returned
Dim strTmp As String
Dim intPos As Integer
Dim strFirstName
Dim strLastName

strTmp = Trim$(strIn)
intPos = InStr(strTmp, chrDelimit)

If intPos = 0 Then
GetFirstWord = strTmp
Else
GetFirstWord = left$(strTmp, intPos – 1)
strLastName = Mid(strTmp, intPos + 1)
End If

End Function

Function GetLastWord(strIn As String, chrDelimit As String) As String
‘ Parameters : strIn – string to search
‘ chrDelimit – character delimiter
‘ if delimiter is not found null is returned
Dim strTmp As String
Dim intPos As Integer
Dim strFirstName
Dim strLastName

strTmp = Trim$(strIn)
intPos = InStr(strTmp, chrDelimit)

If intPos = 0 Then
GetLastWord = “”
Else
GetLastWord = Mid(strTmp, intPos + Len(chrDelimit))
End If

End Function

Try the following functions. GetFirstWord takes your input string & a delimiter string & returns your input string up to the first occurrence of your delimiter string. GetLastWord is similar but returns your input string after the first occurrence of your delimiter string. In your example the delimiter string would be ” “.

Function GetFirstWord(strIn As String, chrDelimit As String) As String
‘ Parameters : strIn – string to search
‘ chrDelimit – character delimiter
‘ if delimiter is not found whole string is returned
Dim strTmp As String
Dim intPos As Integer
Dim strFirstName
Dim strLastName

strTmp = Trim$(strIn)
intPos = InStr(strTmp, chrDelimit)

If intPos = 0 Then
GetFirstWord = strTmp
Else
GetFirstWord = left$(strTmp, intPos – 1)
strLastName = Mid(strTmp, intPos + 1)
End If

End Function

Function GetLastWord(strIn As String, chrDelimit As String) As String
‘ Parameters : strIn – string to search
‘ chrDelimit – character delimiter
‘ if delimiter is not found null is returned
Dim strTmp As String
Dim intPos As Integer
Dim strFirstName
Dim strLastName

strTmp = Trim$(strIn)
intPos = InStr(strTmp, chrDelimit)

If intPos = 0 Then
GetLastWord = “”
Else
GetLastWord = Mid(strTmp, intPos + Len(chrDelimit))
End If

End Function

If you mean that when you create a new record you want to put todays date in, say, a field called [CreationDate] then you can set the default value for that field =Now() if you want date & time or =Date() if you just want the date.

Glad to help!

If you define your fields as Fixed instead of General Number it works as you want

You Americans are such sticklers for etiquette! Well I asked the Queen at dinner last night & she said you were technically correct but what I wanted to do was fine! PS the Duke of Edinburgh agreed too.

I fully accept all your comments. I understand the faults in the way I have structured the data but I was young then…… It reminds me of the old Irish joke along the lines of ‘How do I get to Dublin?’ ‘I wouldn’t start from here!’
Hans solution is perfect. Just what I was striving for before I got lost in the complexity of writing an expression. Why I didn’t think of using a seperate query I’ll never know.

Thanks to you both for your interest & help.

I have used Francois solution which he helped me with some time ago & have been very happy & impressed with it!

Changing the data type is the best solution.

If you really want to keep it as text you could parse out the year in a query with Right(YourData, 4) & then sort on the result. This would order on the year only.

Changing the data type is the best solution.

If you really want to keep it as text you could parse out the year in a query with Right(YourData, 4) & then sort on the result. This would order on the year only.