Search results

Hi - Take a look at the last post in this thread. You can easily modify the example to populate a table with weekdays of your choosing (with Sunday = 1 thru Saturday = 7. HTH - Bob

Hi - Still fighting with this issue. Any chance someone could post some sample SQL. Thanks in advance - Bob

Hi - From the debug (immediate) window, here are three examples, using the same one-liner. See if this would be of assistance. x = "90036-1234" ? iif(len(rtrim(x))<=6, left(x,5), x) 90036-1234 x = "91234-" ? iif(len(rtrim(x))<=6, left(x,5), x) 91234 x = "92486" ? iif(len(rtrim(x))<=6...

Referring back to my post #5, the code provided is not dependent on a specific number of characters between the 'P' and the '-'. Try copying/pasting the function cited to a new module, then experiment using the one-liner, i.e.: ? removealphas(iif(instr(x, "-")>0, left(x, instr(x, "-")-1), x))...

Hi - Take a look at the RemoveAlphas() function at this Microsoft site: http://support.microsoft.com/kb/210537 To use the code to convert your P4352342-1 to 4352342, you could try something like (from the debug (immediate) window): x = "P4352342-11" ? removealphas(iif(instr(x, "-")>0...

Hi - See my post at this thread. HTH - Bob

Hi - The following will allow you to either or subtract business days (Monday - Friday). It doesn't, as written, include provisions for holidays: Function UpBusDays3(pstart As Date, _ pNum As Integer, _ Optional pAdd As Boolean = True) As Date...

Hi - Try the Month() function. Example: x = date() ? x 'US date format 7/2/2011 ? month(x) 7 Or, if you needed to show 2 digits: ? format(month(x), "00") 07 Hope that helps - Bob

Hi - Looks like you have it under control. Here's a little function, using your logic, which may make it a little easier to apply it in a query: Public Function Num2Time(x As Double) As String 'Purpose: Convert a digital number to a time, e.g. 9.3 converts to 09:30 'Refer to...

Hi - Welcome to the forum! Please don't take this personally but, in my opinion you've got a monster on your hands due to table design. ".....They are both time stored as number format but the number 9 represents 09:00 and the number 17.3 respresents 17:30..." Don't understand why you are...

Hi - Welcome to the forum. I'm not sure exactly how you are inputting your text date (an example would be helpful). In most cases, the DateValue() function will accurately convert your text date. Here are a couple of examples from the debug (immediate) window...

Hi - Here's another method that you might want to play with. From the debug (immediate) window: Hope that helps. Best wishes: Bob

Hi - First, I'd suggest you try the debug window info in my original post. It should work (it worked for me). Next, evaluate the data type of your effective date. Where's that coming from? Is it a true date or another text date? If that's the case, you'll need to use the...

Hi - Welcome to the forum! With the information you've provided, you'll need to use the Iif() function, and the Datevalue() function, which will convert your text date to a true date. Suggest you look both of them up in the help file. Here are a couple of examples from the...

Hi - Suffering severe brain-cramp today. Have two queries: 1) For a specified month, totals various home expense categories, e.g., Insurance, Utilities, etc.. 2) For a specified month, totals payment method, e.g., cash, check, credit card, etc., for each expense category. The problem --...

Hi - Check-0ut this post: http://www.access-programmers.co.uk/forums/showthread.php?t=72063&highlight=quarter Best wishes - Bob

Thanks Brian - I apparently suffered a serious brain cramp. Sorry if I threw unintended confusion into the pot. Best wishes - Bob

Hi - You might try playing around with this (from the debug window): '******************************************** x = "20110315" y = datevalue(mid(x,5,2)& "/" & mid(x, 7,2) & "/" & left(x,4)) z = format(y, "mmmm yyyy") ? z March 2011 '******************************************** Best wishes...

Hi - If you'd care to convert your download to an mdb (I lack the later software), I'd be happy to give it a shot. Best wishes - Bob

Okay. Any chance you can download sample data? Bob