Replies

Thanks Mark!

I have it working now… We’ll be upgrading soon… The boss said “Not until AFTER year end!”

I like your idea about “standard format for incoming data“… BUT
Ever heard of ADP?? ‘Nuff said…

Thanks Mark!

I have it working now… We’ll be upgrading soon… The boss said “Not until AFTER year end!”

I like your idea about “standard format for incoming data“… BUT
Ever heard of ADP?? ‘Nuff said…

I agree with you Hans… When I started in this department I found that there were TONS of make table queries in about every mdb I looked at…. I almost never use them now… Except during the design process… OR when a query is getting way too slow because of the amount of levels of joins and subqueries…

Someone much more experienced than I once told me “If you have to make tables during daily processing… the database design isn’t right… ” …In most cases, if not all, I agree with him…

This seems to be processing in a reasonable amount of time at this point, so I’ll leave it for now…
Again… Thanks a million!

I agree with you Hans… When I started in this department I found that there were TONS of make table queries in about every mdb I looked at…. I almost never use them now… Except during the design process… OR when a query is getting way too slow because of the amount of levels of joins and subqueries…

Someone much more experienced than I once told me “If you have to make tables during daily processing… the database design isn’t right… ” …In most cases, if not all, I agree with him…

This seems to be processing in a reasonable amount of time at this point, so I’ll leave it for now…
Again… Thanks a million!

Okay… It’s settled… I’m a complete idiot today!

I had commented out the last line of Mark’s function… GetMonthPosRev = intPos + 1… because I didn’t want it to add one to the position…
So guess what?… The function was returning 0…

I’m kinda concerned about the efficiency of these functions though… Maybe I’m wrong (Very Probably!!!) but I was stepping through the code and mannn does it do a lot of looping for each string… The query is using this on over 50,000 records… Who knows… I suppose something similar is happening behind the scenes when I use InStr() too… Oh well…

It works!!! …Thank you both VERY VERY MUCH for the help!!!!!!

Okay… It’s settled… I’m a complete idiot today!

I had commented out the last line of Mark’s function… GetMonthPosRev = intPos + 1… because I didn’t want it to add one to the position…
So guess what?… The function was returning 0…

I’m kinda concerned about the efficiency of these functions though… Maybe I’m wrong (Very Probably!!!) but I was stepping through the code and mannn does it do a lot of looping for each string… The query is using this on over 50,000 records… Who knows… I suppose something similar is happening behind the scenes when I use InStr() too… Oh well…

It works!!! …Thank you both VERY VERY MUCH for the help!!!!!!

Thanks Hans… Now it’s returning 0 for the position for every record… I don’t know what’s wrong at the moment but I guess I’ll have to keep trying… It was working find the first way I had it….. except now with this exception…

I really think I need to go forward and check for “Chr(32) MMM Chr(32) and a number” or “Chr(32) MMM and a number”… but right now, I don’t have a clue how…

This HAS to happen at Year End right??

Thanks Hans… Now it’s returning 0 for the position for every record… I don’t know what’s wrong at the moment but I guess I’ll have to keep trying… It was working find the first way I had it….. except now with this exception…

I really think I need to go forward and check for “Chr(32) MMM Chr(32) and a number” or “Chr(32) MMM and a number”… but right now, I don’t have a clue how…

This HAS to happen at Year End right??

Thanks Mark… but I’m still having problems… Since I can’t use InStrRev, I have no clue how I was supposed to adapt this for my purposes…
I must be brain dead today… Sorry…

We really need a smilie for STRESSED OUT!!

Thanks Mark… but I’m still having problems… Since I can’t use InStrRev, I have no clue how I was supposed to adapt this for my purposes…
I must be brain dead today… Sorry…

We really need a smilie for STRESSED OUT!!

Hi Colin…

Why not do something like only showing the user a combo box with the Month name?
ie… They select that the person wants to start in March or May or whatever… Behind the scenes on the form you take that value and fill a date field with the correct date…

If you don’t want the users to be able to input a date and chance that they get it wrong, don’t let them input a date at all…. Only give them a choice…
and PLEASE tell me that they are not going into the tables and making changes as they wish…

Hi Colin…

Why not do something like only showing the user a combo box with the Month name?
ie… They select that the person wants to start in March or May or whatever… Behind the scenes on the form you take that value and fill a date field with the correct date…

If you don’t want the users to be able to input a date and chance that they get it wrong, don’t let them input a date at all…. Only give them a choice…
and PLEASE tell me that they are not going into the tables and making changes as they wish…

Wait a minute… I just realized that I misunderstood… Are they going to be able to choose ANY month in the future??… For example, IF I sign up today, can I start a year and a half from now, on the first of the month?

Wait a minute… I just realized that I misunderstood… Are they going to be able to choose ANY month in the future??… For example, IF I sign up today, can I start a year and a half from now, on the first of the month?

Just a thought, but why don’t you put a combobox on the form that has two values…
FirstOfMonth… DateSerial(Year(Date()),Month(Date()),1)
FirstOfNextMonth… DateSerial(Year(Date()),Month(Date())+1,1)
That way the user would only be able to choose one of the two…

Does that help?