Search results

Re: "In Between" function that behaves similarly to a "Mid String" function Hi - Couple of examples using Paul's suggestion. Provided the beginning and end portions remain the same lengths, this should work for any combination in the middle: x = "1A23abcdefg4A321" ? mid(x, 5, len(x)-9)...

Hi - The DateValue() function returns just the date portion of date/time fields. Examples: tfield = now() ? dtfield 12/2/2009 7:16:03 AM dfield = datevalue(dtfield) ? dfield 12/2/2009 To see how they're stored by Access: ? cdbl(dtfield) 40149.3028125 ? cdbl(dfield) 40149 HTH - Bob

Hi Ankit - If the need for correction will always be in the last element of a string, you can use the InStrRev() function to resolve it, regardless of the length of the string. Example from the debug/immediate window: x = "w14-1245-01" y = left(x, instrrev(x, "-")) & val(mid(x, instrrev(x...

Hi - Lookup the InStr() function. Examples: x = "abcefxyzn" ? instr(x, "xyz") 6 You can then use it in conjunction with the Iif() function. iif(instr(x, "xyz")>0, Action if true, Action if false) HTH - Bob

Hi - As an alternative, try playing with this: Today is: 11/24/2009 ? dateadd("m", -2, date() - day(date())+1) 9/1/2009 place this as criteria for your datefield: >= dateadd("m", -2, date() - day(date())+1) Best wishes - Bob

Hi - I'm not absolutely sure about this, since I don't have a good example to work with. However, think the Val() function might be the answer, e.g. If val(myuser) = val(testuser) Then ... Please post back with your results. Best wishes - Bob

Hi - As an alternative, add two hidden fields to your query: year(dbo_MFS_ORDER.InvoiceDate) year(dbo_MFS_ACCOUNT.CreateDate) In the criteria cell for each: 2008 Bob

Hi - Consider the TimeValue() function. Here are examples from the debug (immediate) window: x = "12:56:37 PM" y = timevalue(x) ? y 12:56:37 PM To show that Access is storing this as a date/time and not a string ? cdbl(y) 0.53931712962963 HTH - Bob

Hi Rose - Glad it worked for you. Best Wishes - Bob

Hi - Take a look at the note in the function I provided: Most likely replacing references to 'w' with 'q' would be a start with working with quarters. Bob

Hi - Without knowing your table structure, it's difficult to provide a precise fix. However, what you'll probably need to do: 1) Create a new query and pull in the fields from your table that you want to be returned. 2) In the appointment date/time field's criteria place: = date() 3) In...

Hi - Try playing with this Public Function fGetDates(pwknum As Integer) As String '************************************************ 'Purpose: Return a 'between' statement ' based on the Monday and Friday ' of an input week number 'Coded by: raskew 'Inputs: ...

Hi - Try playing with this: ? dateadd("s", -1, date()+1) 11/19/2009 11:59:59 PM HTH - Bob

Hi - Here is the function, copied from http://support.microsoft.com/kb/209857 Function Minimum(ParamArray FieldArray() As Variant) ' Source: http://support.microsoft.com/kb/209857 ' Declare the two local variables. Dim I As Integer Dim currentVal As Variant ' Set the variable...

Hi - Good job. Couple of comments: 1) Change MED to MID (probably a typo) 2) In the format you've shown, this is a statemeht that would be used in a query, but, in its present state is not a 'function' . Bob

Hi - Nested Iif() statements can be a problem. One misplaced parenthesis and you're sunk. You might consider the Switch() function, e.g. AgentNo: Switch([strAgentNo]="67322" and Right([strProductCode],2)="12", "29221", [strAgentNo]="29221" and Right([strProductCode],2)="24", "67322", True...

Hi - Take a look at Allen Browne's solution at http://allenbrowne.com/ser-71.html. This allows you to copy/paste a query's SQL to the form, which then converts it to VBA. HTH - Bob

Hi. Welcome to the forum. Since this is homework, I'm not going to give you the complete solution. However, here are some pointers to get you started: 1) Lookup each of the 5 provided functions in the help file. 2) Since your end result depends on whether there is a "-" in the string...

Hi - You can use the Switch() function in a calculated field. Copy/paste this to a new field in your query, substituting your [field names] as appropriate. Switch([numfld]< 125 and [txtfld]= "scooter", "SC125", [numfld]< 125 and [txtfld]<> "scooter", "MC125", [numfld]> 125 and [txtfld]=...

Hi - Try the Mod operator, e.g. x = 7 ? iif(x mod 2 = 0, "Even", "Odd") Odd x = 244 ? iif(x mod 2 = 0, "Even", "Odd") Even HTH - Bob